using System;
using System.Collections.Generic;
using System.Text;
using System.CodeDom;
using System.CodeDom.Compiler;
using System.IO;
using System.Text.RegularExpressions;
using System.Xml;
using System.Data;
using System.Data.SqlClient;

using Destra.Tools.DACGenerator.Config;

namespace Destra.Tools.DACGenerator.CodeGenerators
{
    public class DacCodeGenerator : CodeDomGenerator
    {

        #region Constants

        private const string LOGIC_CLASS_SUFFIX = "Logic";
        private const string LOGIC_CLASS_NAMESPACE = ".BusinessLogic";

        #endregion

        private string m_fileNamespace;
        private string m_className;
        private string m_fullNamespaceWithoutLogicPrefix;
        private DacGeneratorConfig m_dacGeneratorConfig;
        private GenerateOutputType m_generateOutputType = GenerateOutputType.DataAccessWithBusinessLogic;
        private bool hasCommandOutputParameter;
        private QueryInfo.QueryTables m_queryTables;

        public DacCodeGenerator(CodeDomProvider codeDomProvider)
            :
            base(codeDomProvider)
        {

        }

        #region Kanes CodeDom Helper

        public static class Helper
        {

            public static readonly string BUSINESS_ENTITY_FIELD_PREFIX = "m_";
            public static readonly string BUSINESS_ENTITY_FIELD_SUFFIX = "";

            #region Create Variable

            public static CodeMemberField CreateVariable(Type variableType, string variableName, MemberAttributes scope)
            {
                return CreateVariable(variableType.ToString(), variableName, scope, true);
            }

            public static CodeMemberField CreateVariable(string variableType, string variableName, MemberAttributes scope)
            {
                return CreateVariable(variableType, variableName, scope, true);
            }

            public static CodeMemberField CreateVariable(string variableType, string variableName, MemberAttributes scope, bool usePrefixAndSuffix)
            {
                if (usePrefixAndSuffix)
                {
                    variableName = BUSINESS_ENTITY_FIELD_PREFIX + FormatCamel(variableName) + BUSINESS_ENTITY_FIELD_SUFFIX;
                }
                else
                {
                    variableName = FormatColumnName(variableName);
                }

                CodeMemberField cmf = new CodeMemberField(variableType, variableName);
                cmf.Attributes = scope;
                return cmf;
            }

            public static string GetVariableName(string propertyName)
            {
                return BUSINESS_ENTITY_FIELD_PREFIX + FormatCamel(propertyName) + BUSINESS_ENTITY_FIELD_SUFFIX;
            }

            #endregion

            #region Create Property

            public static CodeMemberProperty CreateProperty(Type propertyType, string propertyName, MemberAttributes scope)
            {
                return CreateProperty(propertyType.ToString(), propertyName, scope);
            }

            public static CodeMemberProperty CreateProperty(string propertyType, string propertyName, MemberAttributes scope)
            {
                string internalPropertyName = FormatPascal(propertyName);
                propertyName = BUSINESS_ENTITY_FIELD_PREFIX + FormatCamel(propertyName) + BUSINESS_ENTITY_FIELD_SUFFIX;

                CodeMemberProperty prop = new CodeMemberProperty();
                prop.HasGet = true;
                prop.HasSet = true;
                prop.Attributes = scope;
                prop.Name = internalPropertyName;
                prop.Type = new CodeTypeReference(propertyType);
                prop.GetStatements.Add(new CodeMethodReturnStatement(new CodeSnippetExpression(propertyName))); // (new CodeFieldReferenceExpression(new CodePrimitiveExpression(), fieldName)));
                prop.SetStatements.Add(CreateVariableAssignment(internalPropertyName, "value"));

                return prop;
            }

            #endregion

            #region Create Method

            public static CodeMemberMethod CreateMethod(Type fieldType, string methodName, MemberAttributes scope)
            {
                return CreateMethod(fieldType.ToString(), methodName, null, scope);
            }

            public static CodeMemberMethod CreateMethod(Type fieldType, string methodName, CodeParameterDeclarationExpressionCollection parameters, MemberAttributes scope)
            {
                return CreateMethod(fieldType.ToString(), methodName, parameters, scope);
            }

            public static CodeMemberMethod CreateMethod(string fieldType, string methodName, CodeParameterDeclarationExpressionCollection parameters, MemberAttributes scope)
            {
                CodeMemberMethod cmm = new CodeMemberMethod();
                cmm.Name = methodName;
                if (parameters != null) { cmm.Parameters.AddRange(parameters); }
                cmm.ReturnType = new CodeTypeReference(fieldType);
                cmm.Attributes = scope;
                return cmm;
            }

            #endregion

            #region Create Variable

            public static string GetCSharpType(Type dataType, bool isNullable)
            {
                if (dataType.Equals(typeof(System.String)))
                {
                    return "string";
                }
                if (dataType.Equals(typeof(System.Int32)))
                {
                    if (isNullable)
                    {
                        return "Nullable<int>";
                    }
                    else
                    {
                        return "int";
                    }
                }
                if (isNullable)
                {
                    return dataType.ToString();
                }
                else
                {
                    return String.Format("Nullable<{0}>", dataType.ToString());
                }
            }

            public static CodeVariableDeclarationStatement CreateVariable(Type dataType, string name)
            {
                // Get a CodeTypeReference for the Type 
                // of the variable we are about to
                // create.   This will allow us not to 
                // have to get bogged down in the
                // language specific details of specifying 
                // data types.
                CodeTypeReference tr = new CodeTypeReference(dataType);

                // The CodeVariableDeclarationStatement 
                // will allow us to not have to
                // worry about such details as whether 
                // the Data Type or the variable name
                // comes first or whether or not a key 
                // word such  as Dim is required.
                CodeVariableDeclarationStatement Declaration = new CodeVariableDeclarationStatement(tr, name);

                // The CodeObjectCreateExpression handles 
                // all of the details for calling
                // constructors.   In most cases, this 
                // will be new, but sometimes it is New.
                // At any rate, we don't want to  have to 
                // worry about such details.
                CodeObjectCreateExpression newStatement = new
                CodeObjectCreateExpression();

                // Here we specify the object whose 
                // constructor we want to invoke.
                newStatement.CreateType = tr;

                // Here we specify that variable will be 
                // initialized by calling its constructor.
                Declaration.InitExpression = newStatement;
                return Declaration;
            }

            #endregion

            #region Set Variable

            // assign a value to a member field
            public static CodeAssignStatement CreateVariableAssignment(string assignFrom, string assignTo)
            {
                assignFrom = BUSINESS_ENTITY_FIELD_PREFIX + FormatCamel(assignFrom) + BUSINESS_ENTITY_FIELD_SUFFIX;
                return new CodeAssignStatement(new CodeSnippetExpression(assignFrom), new CodeArgumentReferenceExpression(assignTo)); // new CodeThisReferenceExpression(), assignTo), new CodeArgumentReferenceExpression(assignFrom));
            }

            // assign a value to a member field
            public static CodeAssignStatement CreateVariableAssignmentForMethod(string assignFrom, string assignTo)
            {
                assignFrom = BUSINESS_ENTITY_FIELD_PREFIX + FormatCamel(assignFrom) + BUSINESS_ENTITY_FIELD_SUFFIX;
                return new CodeAssignStatement(new CodeSnippetExpression(assignFrom), new CodeSnippetExpression(assignTo)); // new CodeThisReferenceExpression(), assignTo), new CodeArgumentReferenceExpression(assignFrom));
            }

            #endregion

            #region String Helper

            /// <summary>
            /// Removes any invalid charaters from a column name
            /// </summary>
            /// <param name="original">A string to be cleaned.</param>
            /// <returns>A cleaned string</returns>
            internal static string FormatColumnName(string original)
            {
                return Regex.Replace(original, @"\d", "");
            }

            /// <summary>
            /// Cleans whitespace inside a string.
            /// </summary>
            /// <param name="original">A string to be cleaned.</param>
            /// <returns>A cleaned string.</returns>
            internal static string CleanWhitespace(string original)
            {
                if (original.Length > 0)
                {
                    return Regex.Replace(original, @"[\s]", String.Empty);
                }
                else
                {
                    return String.Empty;
                }
            }

            /// <summary>
            /// Formats a string in Camel case (the first letter is in lower case).
            /// </summary>
            /// <param name="original">A string to be formatted.</param>
            /// <returns>A string in Camel case.</returns>
            internal static string FormatCamel(string original)
            {
                if (original.Length > 0)
                {
                    return original.Substring(0, 1).ToLower() + original.Substring(1);
                }
                else
                {
                    return String.Empty;
                }
            }

            /// <summary>
            /// Formats a string in Pascal case (the first letter is in upper case).
            /// </summary>
            /// <param name="original">A string to be formatted.</param>
            /// <returns>A string in Pascal case.</returns>
            internal static string FormatPascal(string original)
            {
                if (original.Length > 0)
                {
                    return original.Substring(0, 1).ToUpper() + original.Substring(1);
                }
                else
                {
                    return String.Empty;
                }
            }

            internal static string Tab(int level)
            {
                StringBuilder indentBuilder = new StringBuilder();
                for (int i = 0; i < level; i++)
                {
                    indentBuilder.Append("\t");
                }
                return indentBuilder.ToString();
            }

            #endregion

        }

        #endregion

        #region Logging Call

        private CodeStatement GetLoggingCall(string timerVariableName)
        {
            return
                new CodeExpressionStatement(
                    new CodeMethodInvokeExpression(
                        new CodeTypeReferenceExpression(
                            typeof(Destra.Common.Logging.Logger)
                            ),
                        "Log",
                        new CodeExpression[]
							{
							new CodeFieldReferenceExpression(
								new CodeTypeReferenceExpression(
									typeof(Destra.Common.Logging.Category)
									),
								Destra.Common.Logging.Category.DataAccess.ToString()
								),
							new CodeFieldReferenceExpression(
								new CodeTypeReferenceExpression(
									typeof(System.Diagnostics.TraceEventType)
									),
								System.Diagnostics.TraceEventType.Verbose.ToString()
								),
							new CodeFieldReferenceExpression(
								new CodeTypeReferenceExpression(
									typeof(Destra.Common.Logging.Priority)
									),
								Destra.Common.Logging.Priority.Normal.ToString()
								),
							new CodeMethodInvokeExpression(
								new CodeTypeReferenceExpression(
									typeof(string)
									),
								"Format",
								new CodeExpression[]
									{
									new CodePrimitiveExpression(
										"{0} execution time {1}s"
										),
									new CodePrimitiveExpression(
										m_className
										),
									new CodePropertyReferenceExpression(
										new CodeVariableReferenceExpression(
											timerVariableName
											),
										"Elapsed.TotalSeconds"
										)
									}
								)
							}
                        )
                    );
        }

        #endregion

        #region Get the CacheKey Dependency List

        private string CacheKeyDependency()
        {
            // use our query execution plan to tell us which tables have been used in the query. We will then expire
            // any cache keys that are dependent on these tables.
            string cacheTables = "";
            foreach (QueryInfo.QueryTables.Table table in m_queryTables.Dependencies)
            {
                if (!string.IsNullOrEmpty(table.Inline))
                {
                    cacheTables += string.Format("\"{0}\", ", table.Inline);
                }
            }

            // append a comma if we have some information in our cache tables variable
            cacheTables = cacheTables.Trim();
            if (!string.IsNullOrEmpty(cacheTables) && !cacheTables.EndsWith(","))
            {
                cacheTables += ", ";
            }
            foreach (string tableName in m_dacGeneratorConfig.DacEntityMethodGeneratorConfig.CachingAdditionalTables.Split(",".ToCharArray()))
            {
                if (!string.IsNullOrEmpty(tableName))
                {
                    cacheTables += string.Format("\"{0}\", ", tableName.Trim());
                }
            }

            // remove any trailing ,'s
            cacheTables = cacheTables.Replace("\"\"", "").Trim();
            while (cacheTables.EndsWith(","))
            {
                cacheTables = cacheTables.Substring(0, cacheTables.Length - 1);
            }
            return cacheTables;
        }

        #endregion

        #region Cache Expire

        public CodeStatement CacheExpire(int indentAmount)
        {
            if (m_dacGeneratorConfig.DacEntityMethodGeneratorConfig.CachingEnabled && m_dacGeneratorConfig.DacEntityMethodGeneratorConfig.CachingType.Equals("Expire cache"))
            {
                string indent = "";
                for (int i = 0; i < indentAmount; i++)
                {
                    indent += "\t";
                }

                // return the code snippet
                return new CodeSnippetStatement(string.Format("{0}Destra.Common.Caching.DacCacheManager.Instance.Remove({1});", indent, CacheKeyDependency()));
            }
            return null;
        }



        #endregion

        #region Cache Add

        public CodeStatement[] CacheAdd(bool singleRow, int indentAmount)
        {
            if (m_dacGeneratorConfig.DacEntityMethodGeneratorConfig.CachingEnabled && m_dacGeneratorConfig.DacEntityMethodGeneratorConfig.CachingType.Equals("Read from cache"))
            {
                string indent = "";
                for (int i = 0; i < indentAmount; i++)
                {
                    indent += "\t";
                }

                List<CodeSnippetStatement> code = new List<CodeSnippetStatement>();

                code.Add(new CodeSnippetStatement(string.Format("{0}// Add the specific key to our DacCacheManager, which is a key based dependency wrapper for the System.Web.Caching.Cache", indent)));
                code.Add(new CodeSnippetStatement(string.Format("{0}if (!Destra.Common.Caching.DacCacheManager.Instance.Contains(cacheKey))", indent)));
                code.Add(new CodeSnippetStatement(string.Format("{0}{1}", indent, "{")));
                code.Add(new CodeSnippetStatement(string.Format("{0}\t// Any item added to the cache is automatically expired after 30 minutes. This has been hard coded in the Destra.Service.Common.Cachcing namespace", indent)));
                if (singleRow)
                {
                    code.Add(new CodeSnippetStatement(string.Format("{0}\tDestra.Common.Caching.DacCacheManager.Instance.Add(cacheKey, entity, {1});", indent, CacheKeyDependency())));
                }
                else
                {
                    code.Add(new CodeSnippetStatement(string.Format("{0}\tDestra.Common.Caching.DacCacheManager.Instance.Add(cacheKey, entities.ToArray(), {1});", indent, CacheKeyDependency())));
                }
                code.Add(new CodeSnippetStatement(string.Format("{0}{1}", indent, "}")));
                return code.ToArray();
            }
            return null;
        }

        public CodeStatement[] CacheReturn(bool singleRow, List<QueryInfo.Parameter> parameters, string outputType, int indentAmount)
        {
            if (m_dacGeneratorConfig.DacEntityMethodGeneratorConfig.CachingEnabled && m_dacGeneratorConfig.DacEntityMethodGeneratorConfig.CachingType.Equals("Read from cache"))
            {
                string indent = "";
                for (int i = 0; i < indentAmount; i++)
                {
                    indent += "\t";
                }

                string arrayIdentifier = "";
                if (!singleRow)
                {
                    arrayIdentifier = "[]";
                }

                string parametersToCache = "string.Concat(\"DyanmicKeys:\", ";
                foreach (QueryInfo.Parameter parameter in parameters)
                {
                    parametersToCache += string.Format("\"{0}=\", {0}, \" \", ", parameter.FormattedName);

                }
                parametersToCache = parametersToCache.Trim();
                while (parametersToCache.EndsWith(","))
                {
                    parametersToCache = parametersToCache.Substring(0, parametersToCache.Length - 1).Trim();
                }
                parametersToCache += ").Trim()";

                List<CodeSnippetStatement> code = new List<CodeSnippetStatement>();
                code.Add(new CodeSnippetStatement(string.Format("{0}// Create a dynamic cacheKey for this method call", indent)));
                code.Add(new CodeSnippetStatement(string.Format("{0}string cacheKey = \"DacCache.{1}:\" + {2};", indent, outputType, parametersToCache)));
                code.Add(new CodeSnippetStatement(string.Format("{0}if (Destra.Common.Caching.DacCacheManager.Instance.Contains(cacheKey))", indent)));
                code.Add(new CodeSnippetStatement(string.Format("{0}{1}", indent, "{")));
                code.Add(new CodeSnippetStatement(string.Format("{0}\t// Any item added to the cache is automatically expired after 30 minutes. This has been hard coded in the Destra.Service.Common.Cachcing namespace", indent)));
                code.Add(new CodeSnippetStatement(string.Format("{0}\treturn (Destra.Common.Caching.DacCacheManager.Instance[cacheKey] as {1}{2});", indent, outputType, arrayIdentifier)));
                code.Add(new CodeSnippetStatement(string.Format("{0}{1}", indent, "}")));
                return code.ToArray();
            }

            return null;

            // TODO: Put this into the DACGenerator (also add in a check for the FileSystemWatcher to the Destra.Common.Caching)
            //string cacheKey = "ConsoleApplication7.BusinessLogic.OutputEntityWithCachingLogic.ReadEntities";
            //if (Destra.Common.Caching.DacCacheManager.Instance.Contains(cacheKey))
            //{
            //    return ((Destra.Common.Caching.DacCacheManager.Instance["cacheKey"] as ConsoleApplication7.Entity.SampleEntity[]));
            //}

        }

        #endregion

        #region Predicates

        private CodeTypeDeclaration GetPredicates(List<QueryInfo.OutputColumn> outputColumns)
        {
            if (m_dacGeneratorConfig.DacEntityMethodGeneratorConfig.GeneratePredicates && m_generateOutputType == GenerateOutputType.BusinessLogicOnly && !outputColumns.Count.Equals(0))
            {
                string entityName = "";
                if (m_dacGeneratorConfig.DacEntityMethodGeneratorConfig.DacEntityMethodType == DacEntityMethodType.OutputEmbedded)
                {
                    entityName = m_fullNamespaceWithoutLogicPrefix + ".Entity";
                }
                if (m_dacGeneratorConfig.DacEntityMethodGeneratorConfig.DacEntityMethodType == DacEntityMethodType.Output)
                {
                    entityName = m_dacGeneratorConfig.DacEntityMethodGeneratorConfig.EntityName;
                }

                CodeTypeDeclaration predicateClass = new CodeTypeDeclaration("Predicates");
                predicateClass.IsClass = true;
                predicateClass.IsPartial = true;
                predicateClass.Attributes = MemberAttributes.Public | MemberAttributes.Final;
                predicateClass.Comments.Add(new CodeCommentStatement("<summary>", true));
                predicateClass.Comments.Add(new CodeCommentStatement("Predicates for Generic Searching", true));
                predicateClass.Comments.Add(new CodeCommentStatement("</summary>", true));
                predicateClass.StartDirectives.Add(new CodeRegionDirective(CodeRegionMode.Start, "Entity Predicates"));
                predicateClass.EndDirectives.Add(new CodeRegionDirective(CodeRegionMode.End, ""));

                foreach (QueryInfo.OutputColumn col in outputColumns)
                {
                    StringBuilder builder = new StringBuilder();

                    builder.AppendFormat(Helper.Tab(3) + "/// <summary>Predicate to filter the ReadEntities method on the {1} property.</summary>{0}", Environment.NewLine, col.Name);
                    builder.AppendFormat(Helper.Tab(3) + "/// <example>An example of how to call this predicate.{0}", Environment.NewLine);
                    builder.AppendFormat(Helper.Tab(3) + "/// <code>{0}", Environment.NewLine);
                    builder.AppendFormat(Helper.Tab(3) + "/// MyClass.Entity[] entities = Array.FindAll(BusinessLogic.MyClassLogic.ReadEntities(), new BusinessLogic.MyClassLogic.Predicates().FindByMyProperty(0));{0}", Environment.NewLine);
                    builder.AppendFormat(Helper.Tab(3) + "/// </code>{0}", Environment.NewLine);
                    builder.AppendFormat(Helper.Tab(3) + "/// </example>{0}", Environment.NewLine);
                    builder.AppendFormat(Helper.Tab(3) + "public System.Predicate<{0}> FindBy{1}({2} {3})", entityName, Helper.FormatColumnName(col.Name), col.Type.ToString(), Helper.FormatCamel(col.Name));
                    builder.AppendLine();
                    builder.AppendLine(Helper.Tab(3) + "{");
                    builder.AppendFormat(Helper.Tab(4) + "return delegate({0} e)", entityName);
                    builder.AppendLine();
                    builder.AppendLine(Helper.Tab(4) + "{");
                    builder.AppendFormat(Helper.Tab(5) + "return e.{0}.Equals({1});", col.Name, Helper.FormatCamel(col.Name));
                    builder.AppendLine();
                    builder.AppendLine(Helper.Tab(4) + "};");
                    builder.AppendLine(Helper.Tab(3) + "}");

                    // create our predicates for each column
                    predicateClass.Members.Add(new CodeSnippetTypeMember(builder.ToString()));
                }
                predicateClass.Members.Add(new CodeSnippetTypeMember(""));

                return predicateClass;
            }

            return null;
        }

        #endregion

        List<CodeExpression> GetParameterDeclarations(List<QueryInfo.Parameter> parameters)
        {
            List<CodeExpression> parameterDeclarations = new List<CodeExpression>();

            foreach (QueryInfo.Parameter parameter in parameters)
            {
                parameterDeclarations.Add(
                    new CodeVariableReferenceExpression(
                        parameter.FormattedName
                        )
                    );
            }

            return parameterDeclarations;
        }

        CodeMemberMethod GetExecuteReaderMethod(
            string className,
            List<QueryInfo.Parameter> parameters)
        {
            List<CodeExpression> parameterDeclarations = GetParameterDeclarations(parameters);
            parameterDeclarations.Insert(0,
                new CodePropertyReferenceExpression(
                    new CodeThisReferenceExpression(),
                    "Connection"
                    )
                );

            CodeMemberMethod executeReaderMethod = new CodeMemberMethod();
            executeReaderMethod.Name = "ExecuteReader";

            // we only want the execute reader method to be public on output calls (I.E., ExecuteScalar SHOULD NOT have a public ExecuteReader Method)
            if (m_dacGeneratorConfig._DacEntityMethodGeneratorConfig.DacEntityMethodType == DacEntityMethodType.Output || m_dacGeneratorConfig._DacEntityMethodGeneratorConfig.DacEntityMethodType == DacEntityMethodType.OutputEmbedded)
            {
                executeReaderMethod.Attributes = MemberAttributes.Public | MemberAttributes.Final;
            }
            else
            {
                executeReaderMethod.Attributes = MemberAttributes.Private | MemberAttributes.Final;
            }

            executeReaderMethod.ReturnType = new CodeTypeReference(className + ".Reader");

            // TODO create an overloaded method that will default this... sigh
            executeReaderMethod.Parameters.Add(new CodeParameterDeclarationExpression(typeof(bool), "singleRow"));
            AddCommandParameters(executeReaderMethod, parameters);

            executeReaderMethod.Statements.Add(
                new CodeVariableDeclarationStatement(
                    new CodeTypeReference(
                        typeof(SqlCommand)
                        ),
                    "command",
                    new CodePrimitiveExpression(
                        null
                        )
                    )
                );

            executeReaderMethod.Statements.Add(
                new CodeVariableDeclarationStatement(
                    new CodeTypeReference(
                        typeof(SqlDataReader)
                        ),
                    "reader",
                    new CodePrimitiveExpression(
                        null
                        )
                    )
                );

            executeReaderMethod.Statements.Add(
                new CodeVariableDeclarationStatement(
                    new CodeTypeReference(
                        typeof(System.Diagnostics.Stopwatch)
                        ),
                    "timer",
                    new CodeObjectCreateExpression(
                        new CodeTypeReference(
                            typeof(System.Diagnostics.Stopwatch)
                            ),
                        new CodeExpression[0]
                        )
                    )
                );

            List<CodeStatement> codeStatements = new List<CodeStatement>();

            // try to open a connection if it's closed
            codeStatements.Add(new CodeSnippetStatement("\t\t\t\tif (this.Connection.State == System.Data.ConnectionState.Closed)"));
            codeStatements.Add(new CodeSnippetStatement("\t\t\t\t{"));
            codeStatements.Add(new CodeSnippetStatement("\t\t\t\t\tthis.Connection.Open();"));
            codeStatements.Add(new CodeSnippetStatement("\t\t\t\t}"));

            // ok this looks kinda ugly but is needed because we want to dynamically 
            // add in the command output parameters
            codeStatements.Add(new CodeAssignStatement(
                            new CodeVariableReferenceExpression(
                                "command"
                                ),
                            new CodeMethodInvokeExpression(
                                new CodeThisReferenceExpression(),
                                "GetCommand",
                                parameterDeclarations.ToArray()
                                )
                            ));

            codeStatements.Add(new CodeExpressionStatement(
                    new CodeMethodInvokeExpression(
                        new CodeVariableReferenceExpression(
                            "timer"
                            ),
                        "Start",
                        new CodeExpression[0]
                        )
                    ));

            codeStatements.Add(new CodeSnippetStatement("\t\t\t\tif (singleRow)"));
            codeStatements.Add(new CodeSnippetStatement("\t\t\t\t{"));
            codeStatements.Add(new CodeSnippetStatement("\t\t\t\t\treader = command.ExecuteReader(System.Data.CommandBehavior.SingleRow);"));
            codeStatements.Add(new CodeSnippetStatement("\t\t\t\t}"));
            codeStatements.Add(new CodeSnippetStatement("\t\t\t\telse"));
            codeStatements.Add(new CodeSnippetStatement("\t\t\t\t{"));
            codeStatements.Add(new CodeSnippetStatement("\t\t\t\t\treader = command.ExecuteReader();"));
            codeStatements.Add(new CodeSnippetStatement("\t\t\t\t}"));

            if (hasCommandOutputParameter)
            {
                // we have to create an instance of our reader class then populate the output parameters collection then return it back to the caller
                codeStatements.Add(new CodeSnippetStatement(string.Format("\t\t\t\t{0}.Reader stronglyTypedReader = new {0}.Reader(reader);", className)));

                foreach (QueryInfo.Parameter parameter in parameters)
                {
                    if (parameter.CommandOutputVariable)
                    {
                        codeStatements.Add(new CodeSnippetStatement(string.Format("\t\t\t\tstronglyTypedReader.CommandOutputParameters.{0}Parameter = command.Parameters[\"{1}\"];", parameter.Name.Replace("@", ""), parameter.Name)));
                    }
                }

                codeStatements.Add(new CodeSnippetStatement("\t\t\t\treturn stronglyTypedReader;"));
            }
            else
            {
                // simply return a new instance of our strongly typed reader class passing the reader varible
                codeStatements.Add(
                 new CodeMethodReturnStatement(
                     new CodeObjectCreateExpression(
                             new CodeTypeReference(className + ".Reader"), new CodeSnippetExpression("reader")
                         )
                     ));
            }

            #region Finally Statement

            List<CodeStatement> finallyStatements = new List<CodeStatement>();

            // cancel (for speed) and dispose of our command
            finallyStatements.Add(new CodeSnippetStatement("\t\t\t\tif (command != null)"));
            finallyStatements.Add(new CodeSnippetStatement("\t\t\t\t{"));
            finallyStatements.Add(new CodeSnippetStatement("\t\t\t\t\tcommand.Dispose();"));
            finallyStatements.Add(new CodeSnippetStatement("\t\t\t\t}"));

            // expire our cache (if required)
            if (CacheExpire(0) != null)
            {
                finallyStatements.Add(CacheExpire(4));
            }

            // the last thing that we should do is push our timer value to the queue
            finallyStatements.Add(new CodeExpressionStatement(
                            new CodeMethodInvokeExpression(
                                new CodeVariableReferenceExpression(
                                    "timer"
                                    ),
                                "Stop",
                                new CodeExpression[0]
                                )
                            ));

            finallyStatements.Add(GetLoggingCall("timer"));

            #endregion

            executeReaderMethod.Statements.Add(
            new CodeTryCatchFinallyStatement(
                codeStatements.ToArray(),
                new CodeCatchClause[0],
                finallyStatements.ToArray()
                )
            );

            return executeReaderMethod;
        }

        CodeMemberMethod GetExecuteMethod(List<QueryInfo.Parameter> parameters)
        {
            List<CodeExpression> parameterDeclarations = GetParameterDeclarations(parameters);
            parameterDeclarations.Insert(0,
                new CodePropertyReferenceExpression(
                    new CodeThisReferenceExpression(),
                    "Connection"
                    )
                );

            CodeMemberMethod executeMethod = new CodeMemberMethod();

            executeMethod.Name = "Execute";
            executeMethod.Attributes = MemberAttributes.Public | MemberAttributes.Final;

            executeMethod.ReturnType = new CodeTypeReference(
                typeof(int)
                );
            AddCommandParameters(executeMethod, parameters);

            executeMethod.Statements.Add(
                new CodeVariableDeclarationStatement(
                    new CodeTypeReference(
                        typeof(SqlCommand)
                        ),
                    "command",
                    new CodePrimitiveExpression(
                        null
                        )
                    )
                );

            executeMethod.Statements.Add(
                new CodeVariableDeclarationStatement(
                    new CodeTypeReference(
                        typeof(System.Diagnostics.Stopwatch)
                        ),
                    "timer",
                    new CodeObjectCreateExpression(
                        new CodeTypeReference(
                            typeof(System.Diagnostics.Stopwatch)
                            ),
                        new CodeExpression[0]
                        )
                    )
                );

            #region Finally Statement

            List<CodeStatement> finallyStatements = new List<CodeStatement>();

            // cancel (for speed) and dispose of our command
            finallyStatements.Add(new CodeSnippetStatement("\t\t\t\tif (command != null)"));
            finallyStatements.Add(new CodeSnippetStatement("\t\t\t\t{"));
            finallyStatements.Add(new CodeSnippetStatement("\t\t\t\t\tcommand.Dispose();"));
            finallyStatements.Add(new CodeSnippetStatement("\t\t\t\t}"));

            // expire our cache (if required)
            if (CacheExpire(0) != null)
            {
                finallyStatements.Add(CacheExpire(4));
            }

            // the last thing that we should do is push our timer value to the queue
            finallyStatements.Add(new CodeExpressionStatement(
                            new CodeMethodInvokeExpression(
                                new CodeVariableReferenceExpression(
                                    "timer"
                                    ),
                                "Stop",
                                new CodeExpression[0]
                                )
                            ));

            finallyStatements.Add(GetLoggingCall("timer"));

            #endregion

            executeMethod.Statements.Add(
                new CodeTryCatchFinallyStatement(
                    new CodeStatement[]
						{
						new CodeAssignStatement(
							new CodeVariableReferenceExpression(
								"command"
								),
							new CodeMethodInvokeExpression(
								new CodeThisReferenceExpression(),
								"GetCommand",
								parameterDeclarations.ToArray()
								)
							),
						new CodeExpressionStatement(
							new CodeMethodInvokeExpression(
								new CodeVariableReferenceExpression(
									"timer"
									),
								"Start",
								new CodeExpression[0]
								)
							),
						new CodeMethodReturnStatement(
							new CodeMethodInvokeExpression(
								new CodeVariableReferenceExpression(
									"command"
									),
								"ExecuteNonQuery",
								new CodeExpression[0]
								)
							)
						},
                    new CodeCatchClause[0],
                    finallyStatements.ToArray()
                    )
                );

            return executeMethod;
        }

        #region Read Entitie(s) Method

        private CodeMemberMethod GetReadEntitiesMethod(bool singleRow, DacEntityMethodType dacEntityMethodType, GenerateOutputType generateOutputType, List<QueryInfo.Parameter> parameters, List<QueryInfo.OutputColumn> outputColumns, string entityName, bool staticMethod, string comments)
        {
            CodeMemberMethod codeMemberMethod = new CodeMemberMethod();
            string dacName = m_fullNamespaceWithoutLogicPrefix;

            // a dynamic variable to increase the indent size if we are in a concrete class
            string concreteIndent = "";
            if (!staticMethod)
            {
                concreteIndent = "\t";
            }

            #region Comments Summary

            codeMemberMethod.Comments.Add(new CodeCommentStatement("<summary>", true));
            codeMemberMethod.Comments.Add(new CodeCommentStatement(comments, true));
            codeMemberMethod.Comments.Add(new CodeCommentStatement("</summary>", true));

            #endregion

            #region Region statement

            // create our region statement
            codeMemberMethod.StartDirectives.Add(new CodeRegionDirective(CodeRegionMode.Start, comments));
            codeMemberMethod.EndDirectives.Add(new CodeRegionDirective(CodeRegionMode.End, ""));

            #endregion

            if (generateOutputType == GenerateOutputType.BusinessLogicOnly && entityName.Equals("Entity"))
            {
                entityName = dacName + "." + entityName;
            }
            if (dacEntityMethodType == DacEntityMethodType.OutputEmbedded)
            {
                entityName = dacName + ".Entity";
            }

            #region Read From Cache

            // add in our cache return code, the CacheReturn method will check to see if caching is required
            if (staticMethod && CacheReturn(singleRow, parameters, "", 3) != null && !CacheReturn(singleRow, parameters, "", 3).Length.Equals(0))
            {
                codeMemberMethod.Statements.AddRange(CacheReturn(singleRow, parameters, new CodeTypeReference(entityName).BaseType, 3));
            }

            #endregion

            if (singleRow)
            {
                // set the correct method name
                codeMemberMethod.Name = "ReadEntity";

                // set the return type as a single entity
                codeMemberMethod.ReturnType = new CodeTypeReference(entityName);
            }
            else
            {
                // set the multiple ReadEntities method name
                codeMemberMethod.Name = "ReadEntities";

                // return an array of entity objects
                codeMemberMethod.ReturnType = new CodeTypeReference(entityName + "[]");

                // create a generic list to hold our entities
                CodeTypeReference entityList = new CodeTypeReference("System.Collections.Generic.List", new CodeTypeReference(entityName));
                codeMemberMethod.Statements.Add(new CodeVariableDeclarationStatement(entityList, "entities", new CodeObjectCreateExpression(entityList)));
            }

            if (staticMethod)
            {
                codeMemberMethod.Attributes = MemberAttributes.Public | MemberAttributes.Static;
            }
            else
            {
                codeMemberMethod.Attributes = MemberAttributes.Public | MemberAttributes.Final;
            }

            #region Method Attributes

            codeMemberMethod.CustomAttributes.Add(
                new CodeAttributeDeclaration(
                    new CodeTypeReference(
                        typeof(System.ComponentModel.DataObjectMethodAttribute)
                        ),
                    new CodeAttributeArgument[]
						{
						new CodeAttributeArgument(
							new CodeFieldReferenceExpression(
								new CodeTypeReferenceExpression(
									typeof(System.ComponentModel.DataObjectMethodType)
									),
								System.ComponentModel.DataObjectMethodType.Select.ToString()
								)									
							)
						}
                    )
                );

            #endregion

            // create an instance of a dac class if we are a static method
            if (staticMethod)
            {
                codeMemberMethod.Statements.Add(new CodeVariableDeclarationStatement(new CodeTypeReference(dacName), "dac", new CodeObjectCreateExpression(new CodeTypeReference(dacName))));
            }

            #region Method Parameters

            List<CodeExpression> parameterDeclarations = new List<CodeExpression>();

            // flag that we are returning a single row
            parameterDeclarations.Add(new CodeSnippetExpression("false"));

            foreach (QueryInfo.Parameter parameter in parameters)
            {
                parameterDeclarations.Add(new CodeVariableReferenceExpression(parameter.FormattedName));
            }

            #endregion

            codeMemberMethod.Statements.Add(
                new CodeVariableDeclarationStatement(
                    new CodeTypeReference(
                        dacName + ".Reader"
                        ),
                    "reader",
                    new CodePrimitiveExpression(
                        null
                        )
                    )
                );


            List<CodeStatement> iterationCodeStatements = new List<CodeStatement>();
            iterationCodeStatements.Add(
                new CodeVariableDeclarationStatement(
                    new CodeTypeReference(
                            entityName
                        ),
                    "entity",
                    new CodeObjectCreateExpression(
                        new CodeTypeReference(
                            entityName
                            ),
                        new CodeExpression[0]
                        )
                    )
                );

            foreach (QueryInfo.OutputColumn outputColumn in outputColumns)
            {
                iterationCodeStatements.Add(
                    new CodeAssignStatement(
                        new CodePropertyReferenceExpression(
                            new CodeVariableReferenceExpression(
                                "entity"
                                ),
                            outputColumn.Name
                            ),
                        new CodePropertyReferenceExpression(
                            new CodeVariableReferenceExpression(
                                "reader"
                                ),
                            outputColumn.Name
                            )
                        )
                    );
            }

            if (singleRow)
            {
                // add in our (add to cache) the method will check to see if caching is required 
                if (staticMethod && CacheAdd(singleRow, 5) != null && !CacheAdd(singleRow, 5).Length.Equals(0))
                {
                    // we have to add our caching here as the singleRow will cause a return statement inside our loop
                    iterationCodeStatements.AddRange(CacheAdd(singleRow, 5));
                }

                iterationCodeStatements.Add(new CodeMethodReturnStatement(new CodeVariableReferenceExpression("entity")));
            }
            else
            {
                iterationCodeStatements.Add(new CodeExpressionStatement(new CodeMethodInvokeExpression(new CodeVariableReferenceExpression("entities"), "Add", new CodeVariableReferenceExpression("entity"))));
            }

            // choose which dac to call either the sender (if we are using the business logic class
            // or the "this" object identifier
            string dacCallerExpression = "dac";
            if (!staticMethod && m_generateOutputType == GenerateOutputType.BusinessLogicOnly)
            {
                dacCallerExpression = "m_sender";
            }

            #region Create our dynamic Try statement

            // convert our input parameters into a flat string so we can use our code snippet statement
            StringBuilder readerInputParameters = new StringBuilder();
            foreach (QueryInfo.Parameter parameter in parameters)
            {
                readerInputParameters.Append(parameter.FormattedName);
                readerInputParameters.Append(", ");
            }

            List<CodeStatement> tryStatements = new List<CodeStatement>();

            // if we don't have any parameters then we will simply add 2 spaces which will be trimmed anyway
            if (readerInputParameters.Length < 2)
            {
                tryStatements.Add(new CodeSnippetStatement(string.Format(concreteIndent + "\t\t\t\treader = {0}.ExecuteReader({1});", dacCallerExpression, singleRow.ToString().ToLowerInvariant())));
            }
            else
            {
                tryStatements.Add(new CodeSnippetStatement(string.Format(concreteIndent + "\t\t\t\treader = {0}.ExecuteReader({1}, {2});", dacCallerExpression, singleRow.ToString().ToLowerInvariant(), readerInputParameters.ToString(0, readerInputParameters.Length - 2))));
            }
            // if we are a business logic class then set the m_outputParameters variable to be the same as the
            // sender's OutputParameters property
            if (!staticMethod && generateOutputType == GenerateOutputType.BusinessLogicOnly && hasCommandOutputParameter)
            {
                tryStatements.Add(new CodeAssignStatement(new CodeSnippetExpression("m_outputParameters"), new CodeSnippetExpression("m_sender.OutputParameters")));
            }

            tryStatements.Add(
                new CodeIterationStatement(
                    new CodeExpressionStatement(
                        new CodeSnippetExpression(string.Empty)
                        ),
                    new CodeMethodInvokeExpression(
                        new CodeVariableReferenceExpression(
                            "reader"),
                        "Read",
                        new CodeExpression[0]
                        ),
                    new CodeExpressionStatement(
                        new CodeSnippetExpression(string.Empty)
                        ),
                    iterationCodeStatements.ToArray()
                    )
            );

            #endregion

            #region Finally Statement

            List<CodeStatement> finallyStatements = new List<CodeStatement>();
            finallyStatements.Add(new CodeSnippetStatement(concreteIndent + "\t\t\t\tif (reader != null)"));
            finallyStatements.Add(new CodeSnippetStatement(concreteIndent + "\t\t\t\t{"));
            finallyStatements.Add(new CodeSnippetStatement(concreteIndent + "\t\t\t\t\treader.Close();"));

            // we have to close our reader then update our output parameters
            if (!staticMethod)
            {
                // if we don't close our connection the output parameters will not populate (ADO.NET sucks sometimes)
                foreach (QueryInfo.Parameter parameter in parameters)
                {
                    if (parameter.CommandOutputVariable)
                    {
                        finallyStatements.Add(new CodeSnippetStatement(concreteIndent + string.Format("\t\t\t\t\tm_outputParameters.{0} = reader.CommandOutputParameters.{0}Parameter.Value.ToString();", parameter.Name.Replace("@", ""), parameter.Name)));
                    }
                }
            }

            // dispose of our reader
            finallyStatements.Add(new CodeSnippetStatement(concreteIndent + "\t\t\t\t\t(reader as System.IDisposable).Dispose();"));
            finallyStatements.Add(new CodeSnippetStatement(concreteIndent + "\t\t\t\t}"));

            if (staticMethod)
            {
                // dispose of our DAC class too
                finallyStatements.Add(new CodeSnippetStatement("\t\t\t\tif (dac != null)"));
                finallyStatements.Add(new CodeSnippetStatement("\t\t\t\t{"));
                finallyStatements.Add(new CodeSnippetStatement("\t\t\t\t\t(dac as System.IDisposable).Dispose();"));
                finallyStatements.Add(new CodeSnippetStatement("\t\t\t\t}"));
            }

            #endregion

            // add our try/catch/finally statement to the method signature
            codeMemberMethod.Statements.Add(new CodeTryCatchFinallyStatement(tryStatements.ToArray(), new CodeCatchClause[0], finallyStatements.ToArray()));

            if (singleRow)
            {
                // return null
                codeMemberMethod.Statements.Add(new CodeMethodReturnStatement(new CodePrimitiveExpression(null)));
            }
            else
            {
                // add in our (add to cache) the method will check to see if caching is required 
                if (staticMethod && CacheAdd(singleRow, 3) != null && !CacheAdd(singleRow, 3).Length.Equals(0))
                {
                    codeMemberMethod.Statements.AddRange(CacheAdd(singleRow, 3));
                }

                // call our ToArray method
                codeMemberMethod.Statements.Add(new CodeMethodReturnStatement(new CodeMethodInvokeExpression(new CodeVariableReferenceExpression("entities"), "ToArray")));
            }

            AddCommandParameters(codeMemberMethod, parameters);

            return codeMemberMethod;
        }

        #endregion

        CodeMemberMethod GetExecuteEntityMethod(List<QueryInfo.Parameter> parameters, string entityName)
        {
            List<CodeExpression> parameterDeclarations = new List<CodeExpression>();

            parameterDeclarations.Add(
                new CodePropertyReferenceExpression(
                    new CodeThisReferenceExpression(),
                    "Connection"
                    )
                );

            foreach (QueryInfo.Parameter parameter in parameters)
            {
                parameterDeclarations.Add(
                    new CodePropertyReferenceExpression(
                        new CodeVariableReferenceExpression(
                            "entity"
                            ),
                        parameter.Name.TrimStart("@".ToCharArray())
                        )
                    );
            }

            CodeMemberMethod executeEntityMethod = new CodeMemberMethod();
            executeEntityMethod.Name = "Execute";
            executeEntityMethod.Attributes = MemberAttributes.Public | MemberAttributes.Final;
            executeEntityMethod.ReturnType = new CodeTypeReference(
                typeof(int)
                );

            executeEntityMethod.Parameters.Add(
                new CodeParameterDeclarationExpression(
                    new CodeTypeReference(
                        entityName
                        ),
                    "entity"
                    )
                );

            executeEntityMethod.Statements.Add(
                new CodeVariableDeclarationStatement(
                    new CodeTypeReference(
                        typeof(SqlCommand)
                        ),
                    "command",
                    new CodePrimitiveExpression(
                        null
                        )
                    )
                );

            executeEntityMethod.Statements.Add(
                new CodeVariableDeclarationStatement(
                    new CodeTypeReference(
                        typeof(System.Diagnostics.Stopwatch)
                        ),
                    "timer",
                    new CodeObjectCreateExpression(
                        new CodeTypeReference(
                            typeof(System.Diagnostics.Stopwatch)
                            ),
                        new CodeExpression[0]
                        )
                    )
                );

            #region Finally Statement

            List<CodeStatement> finallyStatements = new List<CodeStatement>();

            // cancel (for speed) and dispose of our command
            finallyStatements.Add(new CodeSnippetStatement("\t\t\t\tif (command != null)"));
            finallyStatements.Add(new CodeSnippetStatement("\t\t\t\t{"));
            finallyStatements.Add(new CodeSnippetStatement("\t\t\t\t\tcommand.Dispose();"));
            finallyStatements.Add(new CodeSnippetStatement("\t\t\t\t}"));

            // expire our cache (if required)
            if (CacheExpire(0) != null)
            {
                finallyStatements.Add(CacheExpire(4));
            }

            // the last thing that we should do is push our timer value to the queue
            finallyStatements.Add(new CodeExpressionStatement(
                            new CodeMethodInvokeExpression(
                                new CodeVariableReferenceExpression(
                                    "timer"
                                    ),
                                "Stop",
                                new CodeExpression[0]
                                )
                            ));

            finallyStatements.Add(GetLoggingCall("timer"));

            #endregion

            executeEntityMethod.Statements.Add(
                new CodeTryCatchFinallyStatement(
                    new CodeStatement[]
						{
						new CodeAssignStatement(
							new CodeVariableReferenceExpression(
								"command"
								),
							new CodeMethodInvokeExpression(
								new CodeThisReferenceExpression(),
								"GetCommand",
								parameterDeclarations.ToArray()
								)
							),
						new CodeExpressionStatement(
							new CodeMethodInvokeExpression(
								new CodeVariableReferenceExpression(
									"timer"
									),
								"Start",
								new CodeExpression[0]
								)
							),
						new CodeMethodReturnStatement(
							new CodeMethodInvokeExpression(
								new CodeVariableReferenceExpression(
									"command"
									),
                                "ExecuteNonQuery",
								new CodeExpression[0]
								)
							)
						},
                    new CodeCatchClause[0],
                    finallyStatements.ToArray()
                    )
                );

            return executeEntityMethod;
        }

        CodeMemberMethod GetExecuteReaderEntityMethod(List<QueryInfo.Parameter> parameters, string entityName, string className)
        {
            List<CodeExpression> parameterDeclarations = new List<CodeExpression>();

            parameterDeclarations.Add(
                new CodePropertyReferenceExpression(
                    new CodeThisReferenceExpression(),
                    "Connection"
                    )
                );

            foreach (QueryInfo.Parameter parameter in parameters)
            {
                parameterDeclarations.Add(
                    new CodePropertyReferenceExpression(
                        new CodeVariableReferenceExpression(
                            "entity"
                            ),
                        parameter.Name.TrimStart("@".ToCharArray())
                        )
                    );
            }

            CodeMemberMethod executeEntityMethod = new CodeMemberMethod();
            executeEntityMethod.Name = "ExecuteReader";
            executeEntityMethod.Attributes = MemberAttributes.Public | MemberAttributes.Final;
            //executeEntityMethod.ReturnType = new CodeTypeReference(
            //    typeof(int)
            //    );
            executeEntityMethod.ReturnType = new CodeTypeReference(
                className + ".Reader"
                );

            executeEntityMethod.Parameters.Add(
                new CodeParameterDeclarationExpression(
                    new CodeTypeReference(
                        entityName
                        ),
                    "entity"
                    )
                );

            executeEntityMethod.Statements.Add(
                new CodeVariableDeclarationStatement(
                    new CodeTypeReference(
                        typeof(SqlCommand)
                        ),
                    "command",
                    new CodePrimitiveExpression(
                        null
                        )
                    )
                );

            executeEntityMethod.Statements.Add(
                new CodeVariableDeclarationStatement(
                    new CodeTypeReference(
                        typeof(System.Diagnostics.Stopwatch)
                        ),
                    "timer",
                    new CodeObjectCreateExpression(
                        new CodeTypeReference(
                            typeof(System.Diagnostics.Stopwatch)
                            ),
                        new CodeExpression[0]
                        )
                    )
                );

            List<CodeStatement> finallyStatements = new List<CodeStatement>();
            finallyStatements.Add(
                new CodeConditionStatement(
                    new CodeBinaryOperatorExpression(
                        new CodeVariableReferenceExpression(
                            "command"
                            ),
                        CodeBinaryOperatorType.IdentityInequality,
                        new CodePrimitiveExpression(
                            null
                            )
                        ),
                    new CodeStatement[]
					    {
					    new CodeExpressionStatement(
						    new CodeMethodInvokeExpression(
							    new CodeCastExpression(
								    new CodeTypeReference(
									    typeof(IDisposable)
									    ),
								    new CodeVariableReferenceExpression(
									    "command"
									    )
								    ),
							    "Dispose",
							    new CodeExpression[0]
							    )
						    )
					    }
                    )
            );

            // expire our cache (if required)
            if (CacheExpire(0) != null)
            {
                finallyStatements.Add(CacheExpire(4));
            }

            // the last thing that we should do is push our timer value to the queue
            finallyStatements.Add(new CodeExpressionStatement(
                            new CodeMethodInvokeExpression(
                                new CodeVariableReferenceExpression(
                                    "timer"
                                    ),
                                "Stop",
                                new CodeExpression[0]
                                )
                            ));

            finallyStatements.Add(GetLoggingCall("timer"));

            executeEntityMethod.Statements.Add(
                new CodeTryCatchFinallyStatement(
                    new CodeStatement[]
						{
						new CodeAssignStatement(
							new CodeVariableReferenceExpression(
								"command"
								),
							new CodeMethodInvokeExpression(
								new CodeThisReferenceExpression(),
								"GetCommand",
								parameterDeclarations.ToArray()
								)
							),
						new CodeExpressionStatement(
							new CodeMethodInvokeExpression(
								new CodeVariableReferenceExpression(
									"timer"
									),
								"Start",
								new CodeExpression[0]
								)
							),
						new CodeMethodReturnStatement(
							new CodeObjectCreateExpression(
								new CodeTypeReference(
									className + ".Reader"
									),
								new CodeExpression[]
									{
									new CodeMethodInvokeExpression(
										new CodeVariableReferenceExpression(
											"command"
											),
										"ExecuteReader",
										new CodeExpression[0]
										)
									}
								)
							)
						},
                    new CodeCatchClause[0],
                    finallyStatements.ToArray()
                    )
                );

            return executeEntityMethod;
        }

        #region Execute Scalar

        CodeMemberMethod GetExecuteScalarMethod(
            List<QueryInfo.OutputColumn> outputColumns,
            List<QueryInfo.Parameter> parameters)
        {
            List<CodeExpression> parameterDeclarations = GetParameterDeclarations(parameters);
            parameterDeclarations.Insert(0,
                new CodePropertyReferenceExpression(
                    new CodeThisReferenceExpression(),
                    "Connection"
                    )
                );

            CodeMemberMethod executeScalarMethod = new CodeMemberMethod();
            executeScalarMethod.Name = "ExecuteScalar";
            executeScalarMethod.Attributes = MemberAttributes.Public | MemberAttributes.Final;
            executeScalarMethod.ReturnType = new CodeTypeReference(
            outputColumns[0].Type
            );

            AddCommandParameters(executeScalarMethod, parameters);

            executeScalarMethod.Statements.Add(
                new CodeVariableDeclarationStatement(
                    new CodeTypeReference(
                        typeof(SqlCommand)
                        ),
                    "command",
                    new CodePrimitiveExpression(
                        null
                        )
                    )
                );

            executeScalarMethod.Statements.Add(
                new CodeVariableDeclarationStatement(
                    new CodeTypeReference(
                        typeof(System.Diagnostics.Stopwatch)
                        ),
                    "timer",
                    new CodeObjectCreateExpression(
                        new CodeTypeReference(
                            typeof(System.Diagnostics.Stopwatch)
                            ),
                        new CodeExpression[0]
                        )
                    )
                );

            #region Finally Statement

            List<CodeStatement> finallyStatements = new List<CodeStatement>();

            // cancel (for speed) and dispose of our command
            finallyStatements.Add(new CodeSnippetStatement("\t\t\t\tif (command != null)"));
            finallyStatements.Add(new CodeSnippetStatement("\t\t\t\t{"));
            finallyStatements.Add(new CodeSnippetStatement("\t\t\t\t\tcommand.Dispose();"));
            finallyStatements.Add(new CodeSnippetStatement("\t\t\t\t}"));

            // expire our cache (if required)
            if (CacheExpire(0) != null)
            {
                finallyStatements.Add(CacheExpire(4));
            }

            // the last thing that we should do is push our timer value to the queue
            finallyStatements.Add(new CodeExpressionStatement(
                            new CodeMethodInvokeExpression(
                                new CodeVariableReferenceExpression(
                                    "timer"
                                    ),
                                "Stop",
                                new CodeExpression[0]
                                )
                            ));

            finallyStatements.Add(GetLoggingCall("timer"));

            #endregion

            executeScalarMethod.Statements.Add(
                new CodeTryCatchFinallyStatement(
                    new CodeStatement[]
						{
						new CodeAssignStatement(
							new CodeVariableReferenceExpression(
								"command"
								),
							new CodeMethodInvokeExpression(
								new CodeThisReferenceExpression(),
								"GetCommand",
								parameterDeclarations.ToArray()
								)
							),
						new CodeExpressionStatement(
							new CodeMethodInvokeExpression(
								new CodeVariableReferenceExpression(
									"timer"
									),
								"Start",
								new CodeExpression[0]
								)
							),
						new CodeMethodReturnStatement(
							new CodeCastExpression(
								new CodeTypeReference(
									outputColumns[0].Type
									),
								new CodeMethodInvokeExpression(
									new CodeVariableReferenceExpression(
										"command"
										),
									"ExecuteScalar",
									new CodeExpression[0]
									)
								)
							)
						},
                    new CodeCatchClause[0],
                    finallyStatements.ToArray()
                    )
                );

            return executeScalarMethod;
        }

        #endregion

        #region Execute Scalar Entity

        CodeMemberMethod GetExecuteScalarEntityMethod(List<QueryInfo.OutputColumn> outputColumns, List<QueryInfo.Parameter> parameters, string entityName)
        {
            List<CodeExpression> parameterDeclarations = new List<CodeExpression>();

            parameterDeclarations.Add(
                new CodePropertyReferenceExpression(
                    new CodeThisReferenceExpression(),
                    "Connection"
                    )
                );

            foreach (QueryInfo.Parameter parameter in parameters)
            {
                parameterDeclarations.Add(
                    new CodePropertyReferenceExpression(
                        new CodeVariableReferenceExpression(
                            "entity"
                            ),
                        parameter.Name.TrimStart("@".ToCharArray())
                        )
                    );
            }

            CodeMemberMethod executeScalarEntityMethod = new CodeMemberMethod();
            executeScalarEntityMethod.Name = "ExecuteScalar";
            executeScalarEntityMethod.Attributes = MemberAttributes.Public | MemberAttributes.Final;
            executeScalarEntityMethod.ReturnType = new CodeTypeReference(
                typeof(int)
                );

            executeScalarEntityMethod.Parameters.Add(
                new CodeParameterDeclarationExpression(
                    new CodeTypeReference(
                        entityName
                        ),
                    "entity"
                    )
                );

            executeScalarEntityMethod.Statements.Add(
                new CodeVariableDeclarationStatement(
                    new CodeTypeReference(
                        typeof(SqlCommand)
                        ),
                    "command",
                    new CodePrimitiveExpression(
                        null
                        )
                    )
                );

            executeScalarEntityMethod.Statements.Add(
                new CodeVariableDeclarationStatement(
                    new CodeTypeReference(
                        typeof(System.Diagnostics.Stopwatch)
                        ),
                    "timer",
                    new CodeObjectCreateExpression(
                        new CodeTypeReference(
                            typeof(System.Diagnostics.Stopwatch)
                            ),
                        new CodeExpression[0]
                        )
                    )
                );

            #region Finally Statement

            List<CodeStatement> finallyStatements = new List<CodeStatement>();

            // cancel (for speed) and dispose of our command
            finallyStatements.Add(new CodeSnippetStatement("\t\t\t\tif (command != null)"));
            finallyStatements.Add(new CodeSnippetStatement("\t\t\t\t{"));
            finallyStatements.Add(new CodeSnippetStatement("\t\t\t\t\tcommand.Dispose();"));
            finallyStatements.Add(new CodeSnippetStatement("\t\t\t\t}"));

            // expire our cache (if required)
            if (CacheExpire(0) != null)
            {
                finallyStatements.Add(CacheExpire(4));
            }

            // the last thing that we should do is push our timer value to the queue
            finallyStatements.Add(new CodeExpressionStatement(
                            new CodeMethodInvokeExpression(
                                new CodeVariableReferenceExpression(
                                    "timer"
                                    ),
                                "Stop",
                                new CodeExpression[0]
                                )
                            ));

            finallyStatements.Add(GetLoggingCall("timer"));

            #endregion

            executeScalarEntityMethod.Statements.Add(
                new CodeTryCatchFinallyStatement(
                    new CodeStatement[]
						{
						new CodeAssignStatement(
							new CodeVariableReferenceExpression(
								"command"
								),
							new CodeMethodInvokeExpression(
								new CodeThisReferenceExpression(),
								"GetCommand",
								parameterDeclarations.ToArray()
								)
							),
						new CodeExpressionStatement(
							new CodeMethodInvokeExpression(
								new CodeVariableReferenceExpression(
									"timer"
									),
								"Start",
								new CodeExpression[0]
								)
							),
						new CodeMethodReturnStatement(
							new CodeCastExpression(
								new CodeTypeReference(
									outputColumns[0].Type
									),
								new CodeMethodInvokeExpression(
									new CodeVariableReferenceExpression(
										"command"
										),
									"ExecuteScalar",
									new CodeExpression[0]
									)
								)
							)
						},
                    new CodeCatchClause[0],
                    finallyStatements.ToArray()
                    )
                );

            return executeScalarEntityMethod;
        }

        #endregion

        CodeMemberMethod GetExecuteNonQueryMethod(
    List<QueryInfo.OutputColumn> outputColumns,
    List<QueryInfo.Parameter> parameters)
        {
            List<CodeExpression> parameterDeclarations = GetParameterDeclarations(parameters);
            parameterDeclarations.Insert(0,
                new CodePropertyReferenceExpression(
                    new CodeThisReferenceExpression(),
                    "Connection"
                    )
                );

            CodeMemberMethod executeScalarMethod = new CodeMemberMethod();
            executeScalarMethod.Name = "ExecuteNonQuery";
            executeScalarMethod.Attributes = MemberAttributes.Public | MemberAttributes.Final;
            executeScalarMethod.ReturnType = new CodeTypeReference(
            outputColumns[0].Type
            );

            AddCommandParameters(executeScalarMethod, parameters);

            executeScalarMethod.Statements.Add(
                new CodeVariableDeclarationStatement(
                    new CodeTypeReference(
                        typeof(SqlCommand)
                        ),
                    "command",
                    new CodePrimitiveExpression(
                        null
                        )
                    )
                );

            executeScalarMethod.Statements.Add(
                new CodeVariableDeclarationStatement(
                    new CodeTypeReference(
                        typeof(System.Diagnostics.Stopwatch)
                        ),
                    "timer",
                    new CodeObjectCreateExpression(
                        new CodeTypeReference(
                            typeof(System.Diagnostics.Stopwatch)
                            ),
                        new CodeExpression[0]
                        )
                    )
                );

            executeScalarMethod.Statements.Add(
                new CodeTryCatchFinallyStatement(
                    new CodeStatement[]
						{
						new CodeAssignStatement(
							new CodeVariableReferenceExpression(
								"command"
								),
							new CodeMethodInvokeExpression(
								new CodeThisReferenceExpression(),
								"GetCommand",
								parameterDeclarations.ToArray()
								)
							),
						new CodeExpressionStatement(
							new CodeMethodInvokeExpression(
								new CodeVariableReferenceExpression(
									"timer"
									),
								"Start",
								new CodeExpression[0]
								)
							),
						new CodeMethodReturnStatement(
							new CodeCastExpression(
								new CodeTypeReference(
									outputColumns[0].Type
									),
								new CodeMethodInvokeExpression(
									new CodeVariableReferenceExpression(
										"command"
										),
									"ExecuteNonQuery",
									new CodeExpression[0]
									)
								)
							)
						},
                    new CodeCatchClause[0],
                    new CodeStatement[]
						{
                            new CodeExpressionStatement(
                            new CodeMethodInvokeExpression(
                                new CodeVariableReferenceExpression(
                                    "timer"
                                    ),
                                "Stop",
                                new CodeExpression[0]
                                )
                            ),
						GetLoggingCall(
							"timer"
							),
						new CodeConditionStatement(
							new CodeBinaryOperatorExpression(
								new CodeVariableReferenceExpression(
									"command"
									),
								CodeBinaryOperatorType.IdentityInequality,
								new CodePrimitiveExpression(
									null
									)
								),
							new CodeStatement[]
								{
								new CodeExpressionStatement(
									new CodeMethodInvokeExpression(
										new CodeCastExpression(
											new CodeTypeReference(
												typeof(IDisposable)
												),
											new CodeVariableReferenceExpression(
												"command"
												)
											),
										"Dispose",
										new CodeExpression[0]
										)
									)
								}
							)
						}
                    )
                );

            return executeScalarMethod;
        }

        public CodeTypeDeclaration GetEntityClass(List<QueryInfo.OutputColumn> outputColumns)
        {
            CodeTypeDeclaration codeTypeDeclaration = new CodeTypeDeclaration();
            codeTypeDeclaration.Attributes = MemberAttributes.Public;
            codeTypeDeclaration.IsPartial = false;
            codeTypeDeclaration.Name = "Entity";

            // add the interfaces (if there are any)
            if (!string.IsNullOrEmpty(m_dacGeneratorConfig.DacEntityMethodGeneratorConfig.EntityInterfaces))
            {
                foreach (string interfaceName in m_dacGeneratorConfig.DacEntityMethodGeneratorConfig.EntityInterfaces.Split(",".ToCharArray()))
                {
                    codeTypeDeclaration.BaseTypes.Add(interfaceName);
                }
            }

            // mark the class as Serializable so that it can be used with ASP.NET viewstate
            codeTypeDeclaration.CustomAttributes.Add(
                new CodeAttributeDeclaration(
                    new CodeTypeReference(
                        typeof(System.SerializableAttribute)
                        )
                    )
                );

            foreach (QueryInfo.OutputColumn outputColumn in outputColumns)
            {
                CodeDomHelper.AddFieldToClass(
                    codeTypeDeclaration,
                    MemberAttributes.Private,
                    "m_" + outputColumn.Name,
                    outputColumn.Type,
                    outputColumn.Nullable);

                CodeDomHelper.AddSimplePropertyToClass(
                    codeTypeDeclaration,
                    MemberAttributes.Public | MemberAttributes.Final,
                    outputColumn.Name,
                    outputColumn.Type,
                    "m_" + outputColumn.Name,
                    outputColumn.Nullable);
            }

            return codeTypeDeclaration;
        }

        private CodeConstructor GetConnectionConstructor()
        {
            CodeConstructor constructor = new CodeConstructor();
            constructor.Attributes = MemberAttributes.Public;
            constructor.Parameters.Add(
                new CodeParameterDeclarationExpression(
                    new CodeTypeReference(
                        typeof(System.Data.SqlClient.SqlConnection)
                        ),
                    "connection"
                    )
                );
            constructor.Statements.Add(
                new CodeAssignStatement(
                    new CodeFieldReferenceExpression(
                        new CodeThisReferenceExpression(),
                        "m_userConnection"
                        ),
                    new CodeVariableReferenceExpression(
                        "connection"
                        )
                    )
                );

            return constructor;
        }

        private CodeMemberField GetCommandTextField(string sql)
        {
            CodeMemberField field;
            field = Helper.CreateVariable(typeof(string), "commandText", MemberAttributes.Private | MemberAttributes.Final);
            field.InitExpression = new CodePrimitiveExpression(sql.Trim());
            return field;
        }

        private CodeMemberField GetCommandTimeoutField()
        {
            CodeMemberField field;
            field = Helper.CreateVariable(typeof(int), "commandTimeout", MemberAttributes.Private | MemberAttributes.Final);
            field.InitExpression = new CodePrimitiveExpression(30);
            return field;
        }

        private CodeMemberProperty GetCommandTextProperty()
        {
            return Helper.CreateProperty(typeof(string), "CommandText", MemberAttributes.Public | MemberAttributes.Final);
        }

        private CodeMemberProperty GetCommandTimeoutProperty()
        {
            return Helper.CreateProperty(typeof(int), "CommandTimeout", MemberAttributes.Public | MemberAttributes.Final);
        }

        private CodeMemberProperty GetConnectionProperty()
        {
            CodeMemberProperty codeMemberProperty = new CodeMemberProperty();
            codeMemberProperty.Name = "Connection";
            codeMemberProperty.Attributes = MemberAttributes.Override | MemberAttributes.Public;
            codeMemberProperty.Type = new CodeTypeReference(
                typeof(System.Data.SqlClient.SqlConnection)
                );

            codeMemberProperty.GetStatements.Add(
                new CodeConditionStatement(
                    new CodeBinaryOperatorExpression(
                        new CodeFieldReferenceExpression(
                            new CodeThisReferenceExpression(),
                            "m_userConnection"
                            ),
                        CodeBinaryOperatorType.IdentityEquality,
                        new CodePrimitiveExpression(
                            null
                            )
                        ),
                    new CodeStatement[]
						{
						new CodeMethodReturnStatement(
							new CodePropertyReferenceExpression(
								new CodeBaseReferenceExpression(),
								"Connection"
								)
							)
						},
                    new CodeStatement[]
						{
						new CodeMethodReturnStatement(
							new CodeFieldReferenceExpression(
								new CodeThisReferenceExpression(),
								"m_userConnection"
								)
							)
						}
                    )
                );

            return codeMemberProperty;
        }

        CodeTypeDeclaration GetReaderClass(List<QueryInfo.Parameter> parameters, List<QueryInfo.OutputColumn> outputColumns)
        {
            CodeTypeDeclaration codeTypeDeclaration = new CodeTypeDeclaration("Reader");
            codeTypeDeclaration.Attributes = MemberAttributes.Public;
            codeTypeDeclaration.IsClass = true;
            codeTypeDeclaration.IsPartial = true;
            codeTypeDeclaration.BaseTypes.Add(
                new CodeTypeReference(
                    typeof(Destra.Common.DataAccess.ReaderBase)
                    )
                );

            if (hasCommandOutputParameter)
            {
                CodeTypeDeclaration parametersClass = new CodeTypeDeclaration("CommandParameters");
                parametersClass.Attributes = MemberAttributes.Public;
                parametersClass.IsClass = true;
                parametersClass.IsPartial = false;
                codeTypeDeclaration.Members.Add(parametersClass);

                #region CommandParameters class initilization

                CodeMemberField commandParametersField = Helper.CreateVariable("CommandParameters", "commandOutputParameters", MemberAttributes.Private);
                commandParametersField.InitExpression = new CodeSnippetExpression("new CommandParameters()");
                commandParametersField.Comments.Add(new CodeCommentStatement("<summary>Variable for the Command Output Parameters helper class as ADO will not set any parameters until the connection is closed.</summary>", true));
                codeTypeDeclaration.Members.Add(commandParametersField);

                CodeMemberProperty commandParametersProperty = Helper.CreateProperty("CommandParameters", "commandOutputParameters", MemberAttributes.Public | MemberAttributes.Final);
                commandParametersProperty.Comments.Add(new CodeCommentStatement("<summary>Property for the Command Output Parameters helper class as ADO will not set any parameters until the connection is closed.</summary>", true));
                codeTypeDeclaration.Members.Add(commandParametersProperty);

                #endregion

                foreach (QueryInfo.Parameter parameter in parameters)
                {
                    if (parameter.CommandOutputVariable)
                    {
                        // add a Parameter collection property if required
                        CodeMemberProperty parametersProperty = Helper.CreateProperty(typeof(System.Data.SqlClient.SqlParameter), parameter.FormattedName + "Parameter", MemberAttributes.Public | MemberAttributes.Final);
                        parametersProperty.Comments.Add(new CodeCommentStatement("<summary>Required for the Command output parameters as ADO will not set the parameters until the connection is closed.</summary>", true));
                        parametersProperty.StartDirectives.Add(new CodeRegionDirective(CodeRegionMode.Start, "Parameter Property for " + parameter.Name));
                        parametersProperty.EndDirectives.Add(new CodeRegionDirective(CodeRegionMode.End, ""));
                        parametersClass.Members.Add(parametersProperty);

                        // add a Parameter collection variable if required
                        CodeMemberField parametersField = Helper.CreateVariable(typeof(System.Data.SqlClient.SqlParameter), parameter.Name.Replace("@", "") + "Parameter", MemberAttributes.Private);
                        parametersField.Comments.Add(new CodeCommentStatement("<summary>Required for the Command Output Parameters " + parameter.Name + " as ADO will not set this parameter until the connection is closed.</summary>", true));
                        parametersField.StartDirectives.Add(new CodeRegionDirective(CodeRegionMode.Start, "Parameter Variable for " + parameter.Name));
                        parametersField.EndDirectives.Add(new CodeRegionDirective(CodeRegionMode.End, ""));
                        parametersClass.Members.Add(parametersField);
                    }
                }
            }

            // constructor
            CodeConstructor codeConstructor = new CodeConstructor();
            codeConstructor.Attributes = MemberAttributes.Public;
            codeConstructor.Parameters.Add(
                new CodeParameterDeclarationExpression(
                    new CodeTypeReference(
                        typeof(System.Data.SqlClient.SqlDataReader)
                        ),
                    "reader"
                    )
                );
            codeConstructor.BaseConstructorArgs.Add(
                new CodeVariableReferenceExpression(
                    "reader"
                    )
                );
            codeTypeDeclaration.Members.Add(codeConstructor);

            // column accessors
            foreach (QueryInfo.OutputColumn outputColumn in outputColumns)
            {
                CodeMemberProperty codeMemberProperty = new CodeMemberProperty();
                codeMemberProperty.Attributes = MemberAttributes.Public | MemberAttributes.Final;
                codeMemberProperty.Name = outputColumn.Name;

                if ((outputColumn.Type.IsValueType == true) &&
                    (outputColumn.Nullable == true))
                {
                    codeMemberProperty.Type = new CodeTypeReference(
                            "System.Nullable",
                            new CodeTypeReference[]
									{
									new CodeTypeReference(
										outputColumn.Type
										)
									}
                            );
                }
                else
                {
                    codeMemberProperty.Type = new CodeTypeReference(
                            outputColumn.Type
                            );
                }

                if (outputColumn.Nullable == true)
                {
                    codeMemberProperty.GetStatements.Add(
                        new CodeConditionStatement(
                            new CodeBinaryOperatorExpression(
                                new CodeMethodInvokeExpression(
                                    new CodeArrayIndexerExpression(
                                        new CodeThisReferenceExpression(),
                                        new CodeExpression[]
													{
													new CodePrimitiveExpression(outputColumn.Name)
													}
                                        ),
                                    "GetType",
                                    new CodeExpression[0]
                                    ),
                                CodeBinaryOperatorType.IdentityEquality,
                                new CodeTypeOfExpression(
                                    typeof(System.DBNull)
                                    )
                                ),
                            new CodeStatement[]
									{
									outputColumn.Type.IsValueType ?
										new CodeMethodReturnStatement(
											new CodeObjectCreateExpression(
												new CodeTypeReference(
													"System.Nullable",
													new CodeTypeReference[]
														{
														new CodeTypeReference(
															outputColumn.Type
															)
														}
													),
												new CodeExpression[0]
												)
											) :
										new CodeMethodReturnStatement(
											new CodePrimitiveExpression(null)
											)
									},
                            new CodeStatement[]
									{
									new CodeMethodReturnStatement(
										new CodeCastExpression(
											new CodeTypeReference(
												outputColumn.Type
												),
											new CodeArrayIndexerExpression(
												new CodeThisReferenceExpression(),
												new CodeExpression[]
														{
														new CodePrimitiveExpression(outputColumn.Name)
														}
												)
											)
										)
									}
                            )
                        );
                }
                else
                {
                    codeMemberProperty.GetStatements.Add(
                        new CodeMethodReturnStatement(
                            new CodeCastExpression(
                                new CodeTypeReference(
                                    outputColumn.Type
                                    ),
                                new CodeArrayIndexerExpression(
                                    new CodeThisReferenceExpression(),
                                    new CodeExpression[]
												{
												new CodePrimitiveExpression(outputColumn.Name)
												}
                                    )
                                )
                            )
                        );
                }

                codeTypeDeclaration.Members.Add(codeMemberProperty);
            }

            return codeTypeDeclaration;
        }

        CodeTypeDeclaration GetClass(
            string sql,
            List<QueryInfo.Parameter> parameters,
            List<QueryInfo.OutputColumn> outputColumns,
            DacEntityMethodType dacEntityMethodType,
            string entityName,
            bool generateLogging,
            string RunTimeConnectionStringName,
            GenerateOutputType generateOutputType)
        {
            CodeTypeMember codeTypeMember;

            CodeTypeDeclaration codeTypeDeclaration = new CodeTypeDeclaration(m_className);
            codeTypeDeclaration.Attributes = MemberAttributes.Public;
            codeTypeDeclaration.IsClass = true;
            codeTypeDeclaration.IsPartial = true;
            if (generateOutputType == GenerateOutputType.BusinessLogicOnly)
            {
                codeTypeDeclaration.Attributes = MemberAttributes.Public | MemberAttributes.Static;
            }

            if (generateOutputType == GenerateOutputType.DataAccessOnly || generateOutputType == GenerateOutputType.DataAccessWithBusinessLogic)
            {
                codeTypeDeclaration.BaseTypes.Add(
                    new CodeTypeReference(
                        "Destra.Common.DataAccess.DACBase"
                        )
                    );

                // user specified connection
                codeTypeDeclaration.Members.Add(
                    new CodeMemberField(
                        new CodeTypeReference(
                            typeof(System.Data.SqlClient.SqlConnection)
                            ),
                        "m_userConnection"
                        )
                    );
            }

            // create a concrete class to hold our business logic (if there is any)
            CodeTypeDeclaration concreteBusinessLogicClass = new CodeTypeDeclaration("Concrete");
            concreteBusinessLogicClass.Attributes = MemberAttributes.Public;
            concreteBusinessLogicClass.IsClass = true;
            concreteBusinessLogicClass.IsPartial = true;
            concreteBusinessLogicClass.StartDirectives.Add(new CodeRegionDirective(CodeRegionMode.Start, "Concrete Business Logic Class"));
            concreteBusinessLogicClass.EndDirectives.Add(new CodeRegionDirective(CodeRegionMode.End, ""));

            // parameterless constructor
            if (generateOutputType != GenerateOutputType.BusinessLogicOnly)
            {
                CodeConstructor constructor = new CodeConstructor();
                constructor.Attributes = MemberAttributes.Public;
                codeTypeDeclaration.Members.Add(constructor);
            }
            else
            {
                // add our concrete class to the static class
                codeTypeDeclaration.Members.Add(concreteBusinessLogicClass);

                // create a constructor that takes a dac as a sender object
                CodeConstructor constructor = new CodeConstructor();
                constructor.Attributes = MemberAttributes.Public;
                constructor.Parameters.Add(new CodeParameterDeclarationExpression(m_fullNamespaceWithoutLogicPrefix, "sender"));
                constructor.Statements.Add(new CodeSnippetStatement("\t\t\t\tm_sender = sender;"));
                constructor.StartDirectives.Add(new CodeRegionDirective(CodeRegionMode.Start, "Constructor(s)"));
                constructor.EndDirectives.Add(new CodeRegionDirective(CodeRegionMode.End, ""));
                concreteBusinessLogicClass.Members.Add(constructor);

                // create a variable to hold the constructor "sender"
                concreteBusinessLogicClass.Members.Add(Helper.CreateVariable(m_fullNamespaceWithoutLogicPrefix, "sender", MemberAttributes.Private));
            }

            if (generateOutputType == GenerateOutputType.DataAccessOnly || generateOutputType == GenerateOutputType.DataAccessWithBusinessLogic)
            {
                // a constructor that takes a SqlConnection
                codeTypeDeclaration.Members.Add(GetConnectionConstructor());

                // override the Connection property
                codeTypeDeclaration.Members.Add(GetConnectionProperty());

                // add the command timeout field
                codeTypeMember = GetCommandTimeoutField();
                codeTypeDeclaration.Members.Add(codeTypeMember);
                codeTypeMember.StartDirectives.Add(new CodeRegionDirective(CodeRegionMode.Start, "Command Timeout Field"));
                codeTypeMember.EndDirectives.Add(new CodeRegionDirective(CodeRegionMode.End, ""));

                // add the command timeout
                codeTypeMember = GetCommandTimeoutProperty();
                codeTypeDeclaration.Members.Add(codeTypeMember);
                codeTypeMember.StartDirectives.Add(new CodeRegionDirective(CodeRegionMode.Start, "Command Timeout Property"));
                codeTypeMember.EndDirectives.Add(new CodeRegionDirective(CodeRegionMode.End, ""));

                // add the command text field
                codeTypeMember = GetCommandTextField(sql);
                codeTypeDeclaration.Members.Add(codeTypeMember);
                codeTypeMember.StartDirectives.Add(new CodeRegionDirective(CodeRegionMode.Start, "Command Text Field"));
                codeTypeMember.EndDirectives.Add(new CodeRegionDirective(CodeRegionMode.End, ""));

                // add the command text
                codeTypeMember = GetCommandTextProperty();
                codeTypeDeclaration.Members.Add(codeTypeMember);
                codeTypeMember.StartDirectives.Add(new CodeRegionDirective(CodeRegionMode.Start, "Command Text Property"));
                codeTypeMember.EndDirectives.Add(new CodeRegionDirective(CodeRegionMode.End, ""));

                // run time connection string property
                codeTypeDeclaration.Members.Add(GetConnectionStringProperty(RunTimeConnectionStringName));

                // GetCommand (concrete, the static method has been removed)
                codeTypeMember = GetCommandMethod(sql, parameters);
                codeTypeMember.StartDirectives.Add(new CodeRegionDirective(CodeRegionMode.Start, "Concrete GetCommand Method"));
                codeTypeMember.EndDirectives.Add(new CodeRegionDirective(CodeRegionMode.End, ""));
                codeTypeDeclaration.Members.Add(codeTypeMember);
            }

            if (outputColumns.Count > 0)
            {
                if (generateOutputType == GenerateOutputType.DataAccessOnly || generateOutputType == GenerateOutputType.DataAccessWithBusinessLogic)
                {
                    // There are output columns
                    if (dacEntityMethodType == DacEntityMethodType.ExecuteScalar || dacEntityMethodType == DacEntityMethodType.ExecuteScalar)
                    {
                        codeTypeDeclaration.Members.Add(GetExecuteScalarMethod(outputColumns, parameters));
                    }
                    else if (dacEntityMethodType == DacEntityMethodType.ExecuteScalarEntity)
                    {
                        codeTypeDeclaration.Members.Add(GetExecuteScalarMethod(outputColumns, parameters));
                        // add an execute scalar method that takes an entity class as an input
                        codeTypeDeclaration.Members.Add(GetExecuteScalarEntityMethod(outputColumns, parameters, entityName));
                    }
                    else if (dacEntityMethodType == DacEntityMethodType.None)
                    {
                        // call our execute none method
                        codeTypeDeclaration.Members.Add(GetExecuteNonQueryMethod(outputColumns, parameters));
                    }
                    else
                    {
                        codeTypeMember = GetExecuteReaderMethod(codeTypeDeclaration.Name, parameters);
                        codeTypeMember.StartDirectives.Add(new CodeRegionDirective(CodeRegionMode.Start, "Execute reader methods"));
                        codeTypeMember.EndDirectives.Add(new CodeRegionDirective(CodeRegionMode.End, ""));
                        codeTypeDeclaration.Members.Add(codeTypeMember);

                        //codeTypeMember = GetExecuteReaderMethod(codeTypeDeclaration.Name, parameters, false);
                        //codeTypeDeclaration.Members.Add(codeTypeMember);
                    }
                }

                switch (dacEntityMethodType)
                {
                    case DacEntityMethodType.None:
                        break;
                    case DacEntityMethodType.Input:
                        codeTypeDeclaration.Members.Add(GetExecuteReaderEntityMethod(parameters, entityName, codeTypeDeclaration.Name));
                        codeTypeDeclaration.Members.Add(GetExecuteEntityMethod(parameters, entityName));
                        break;

                    case DacEntityMethodType.OutputEmbedded:
                    case DacEntityMethodType.Output:
                        if (generateOutputType == GenerateOutputType.BusinessLogicOnly || generateOutputType == GenerateOutputType.DataAccessWithBusinessLogic)
                        {
                            CodeTypeDeclaration classToUse = codeTypeDeclaration;
                            if (generateOutputType == GenerateOutputType.BusinessLogicOnly)
                            {
                                classToUse = concreteBusinessLogicClass;
                            }

                            // Entity
                            if (generateOutputType == GenerateOutputType.BusinessLogicOnly)
                            {
                                // only create a concrete class in our business logic class
                                classToUse.Members.Add(GetReadEntitiesMethod(true, dacEntityMethodType, generateOutputType, parameters, outputColumns, entityName, false, "A concrete method to return an external strongly typed Entity object"));
                            }
                            codeTypeDeclaration.Members.Add(GetReadEntitiesMethod(true, dacEntityMethodType, generateOutputType, parameters, outputColumns, entityName, true, "A static method to return an external strongly typed Entity object"));

                            // Entities
                            if (generateOutputType == GenerateOutputType.BusinessLogicOnly)
                            {
                                // only create a concrete class in our business logic class
                                classToUse.Members.Add(GetReadEntitiesMethod(false, dacEntityMethodType, generateOutputType, parameters, outputColumns, entityName, false, "A concrete method to return an external strongly typed Entities object array"));
                            }
                            codeTypeDeclaration.Members.Add(GetReadEntitiesMethod(false, dacEntityMethodType, generateOutputType, parameters, outputColumns, entityName, true, "A static method to return an external strongly typed Entities object array"));

                            // add in our predicates
                            if (GetPredicates(outputColumns) != null)
                            {
                                codeTypeDeclaration.Members.Add(GetPredicates(outputColumns));
                            }
                        }

                        if (generateOutputType == GenerateOutputType.DataAccessOnly || generateOutputType == GenerateOutputType.DataAccessWithBusinessLogic)
                        {
                            #region Add Entity Class

                            if (dacEntityMethodType == DacEntityMethodType.OutputEmbedded)
                            {
                                codeTypeMember = GetEntityClass(outputColumns);
                                codeTypeMember.StartDirectives.Add(new CodeRegionDirective(CodeRegionMode.Start, "Entity Class"));
                                codeTypeMember.EndDirectives.Add(new CodeRegionDirective(CodeRegionMode.End, ""));
                                codeTypeDeclaration.Members.Add(codeTypeMember);
                            }

                            #endregion
                        }

                        break;
                    default:
                        break;
                }

                if ((outputColumns.Count > 0) && (dacEntityMethodType == DacEntityMethodType.Output || dacEntityMethodType == DacEntityMethodType.OutputEmbedded))
                {
                    if (generateOutputType == GenerateOutputType.DataAccessOnly || generateOutputType == GenerateOutputType.DataAccessWithBusinessLogic)
                    {
                        // create our reader class
                        CodeTypeDeclaration readerClass = GetReaderClass(parameters, outputColumns);
                        readerClass.StartDirectives.Add(new CodeRegionDirective(CodeRegionMode.Start, "Strongly typed Data Reader class"));
                        readerClass.EndDirectives.Add(new CodeRegionDirective(CodeRegionMode.End, ""));

                        // add the reader class to our DAC class
                        codeTypeDeclaration.Members.Add(readerClass);
                    }
                }
            }
            else
            {
                // There are no output columns
                if (generateOutputType == GenerateOutputType.DataAccessOnly || generateOutputType == GenerateOutputType.DataAccessWithBusinessLogic)
                {
                    codeTypeDeclaration.Members.Add(GetExecuteMethod(parameters));
                }

                if (dacEntityMethodType == DacEntityMethodType.Input)
                {
                    codeTypeDeclaration.Members.Add(GetExecuteEntityMethod(parameters, entityName));
                }
            }

            #region Struct to hold the output parameters

            if (hasCommandOutputParameter)
            {
                // only add the variable initialization if we are a DAC component
                if (generateOutputType != GenerateOutputType.BusinessLogicOnly)
                {
                    // create a new struct to hold the output parameters
                    CodeTypeDeclaration commandOutputParametersStruct = new CodeTypeDeclaration("CommandOutputParameters");
                    commandOutputParametersStruct.IsClass = true;
                    commandOutputParametersStruct.IsPartial = false;
                    commandOutputParametersStruct.Attributes = MemberAttributes.Public | MemberAttributes.Final;
                    commandOutputParametersStruct.StartDirectives.Add(new CodeRegionDirective(CodeRegionMode.Start, "Struct to hold the output parameters from the internal command object"));
                    commandOutputParametersStruct.EndDirectives.Add(new CodeRegionDirective(CodeRegionMode.End, ""));

                    CodeMemberMethod toStringMethod = Helper.CreateMethod(typeof(string), "ToString", MemberAttributes.Public | MemberAttributes.Override);

                    StringBuilder toStringBuilder = new StringBuilder();
                    toStringBuilder.Append("string.Concat(");
                    for (int i = 0; i < parameters.Count; i++)
                    {
                        if (parameters[i].CommandOutputVariable)
                        {
                            toStringBuilder.AppendFormat("\"[{0}] = \", {0}, \", \", ", parameters[i].Name.Replace("@", ""));
                        }
                    }
                    toStringBuilder.Append("\"\")");

                    toStringMethod.Statements.Add(new CodeMethodReturnStatement(new CodeSnippetExpression(toStringBuilder.ToString(0, toStringBuilder.Length))));
                    commandOutputParametersStruct.Members.Add(toStringMethod);

                    // add in the command output columns as properties
                    AddCommandOutputParametersAsFields(commandOutputParametersStruct, parameters);
                    AddCommandOutputParametersAsProperties(commandOutputParametersStruct, parameters);

                    // add our struct to the DAC class
                    codeTypeDeclaration.Members.Add(commandOutputParametersStruct);

                    // create a field and initilization assignment in the DAC class for the struct
                    CodeMemberField outputParametersField = Helper.CreateVariable("CommandOutputParameters", "outputParameters", MemberAttributes.Private);
                    outputParametersField.InitExpression = new CodeSnippetExpression("new CommandOutputParameters()");
                    codeTypeDeclaration.Members.Add(outputParametersField);

                    // add in our property in the DAC class for our struct
                    codeTypeDeclaration.Members.Add(Helper.CreateProperty("CommandOutputParameters", "OutputParameters", MemberAttributes.Public | MemberAttributes.Final));
                }
                else
                {
                    // since we are in a logic class only create an instance of our DAC class struct 
                    concreteBusinessLogicClass.Members.Add(Helper.CreateVariable(m_fullNamespaceWithoutLogicPrefix + ".CommandOutputParameters", "outputParameters", MemberAttributes.Private));

                    // add in our property in the DAC class for our struct
                    concreteBusinessLogicClass.Members.Add(Helper.CreateProperty(m_fullNamespaceWithoutLogicPrefix + ".CommandOutputParameters", "OutputParameters", MemberAttributes.Public | MemberAttributes.Final));
                }
            }

            #endregion

            return codeTypeDeclaration;
        }

        #region Command Output Parameters As Properties

        /// <summary>
        /// Adds the command output parameters as properties.
        /// </summary>
        /// <param name="codeTypeDeclaration">The code type declaration.</param>
        /// <param name="parameters">The parameters.</param>
        private void AddCommandOutputParametersAsProperties(CodeTypeDeclaration codeTypeDeclaration, List<QueryInfo.Parameter> parameters)
        {
            List<CodeMemberProperty> properties = new List<CodeMemberProperty>();
            List<int> parameterIndex = new List<int>();
            for (int i = 0; i < parameters.Count; i++)
            {
                if (parameters[i].CommandOutputVariable)
                {
                    properties.Add(CodeDomHelper.AddSimplePropertyToClass(codeTypeDeclaration, MemberAttributes.Public | MemberAttributes.Final, parameters[i].Name, parameters[i].Type, "m_" + Helper.FormatCamel(parameters[i].Name.Replace("@", "")), parameters[i].Nullable));
                    parameterIndex.Add(i);
                }
            }

            for (int i = 0; i < properties.Count; i++)
            {
                properties[i].Comments.Add(new CodeCommentStatement("<summary>", true));
                properties[i].Comments.Add(new CodeCommentStatement(string.Format("Property that returns the value from the {0} SELECT variable.", parameters[parameterIndex[i]].Name), true));
                properties[i].Comments.Add(new CodeCommentStatement("</summary>", true));

                if (i.Equals(0))
                {
                    properties[i].StartDirectives.Add(new CodeRegionDirective(CodeRegionMode.Start, "Command Output Parameters - Properties"));
                }

                if (i.Equals(properties.Count - 1))
                {
                    properties[i].EndDirectives.Add(new CodeRegionDirective(CodeRegionMode.End, ""));
                }
            }
        }

        #endregion

        #region Command Output Parameters As Fields

        /// <summary>
        /// Adds the command output parameters as fields.
        /// </summary>
        /// <param name="codeTypeDeclaration">The code type declaration.</param>
        /// <param name="parameters">The parameters.</param>
        private void AddCommandOutputParametersAsFields(CodeTypeDeclaration codeTypeDeclaration, List<QueryInfo.Parameter> parameters)
        {
            List<CodeMemberField> fields = new List<CodeMemberField>();
            for (int i = 0; i < parameters.Count; i++)
            {
                if (parameters[i].CommandOutputVariable)
                {
                    fields.Add(CodeDomHelper.AddFieldToClass(codeTypeDeclaration, MemberAttributes.Private | MemberAttributes.Final, "m_" + Helper.FormatCamel(parameters[i].Name.Replace("@", "")), parameters[i].Type, parameters[i].Nullable));
                }
            }

            for (int i = 0; i < fields.Count; i++)
            {
                if (i.Equals(0))
                {
                    fields[i].StartDirectives.Add(new CodeRegionDirective(CodeRegionMode.Start, "Command Output Parameters - Fields"));
                }

                if (i.Equals(fields.Count - 1))
                {
                    fields[i].EndDirectives.Add(new CodeRegionDirective(CodeRegionMode.End, ""));
                }
            }
        }

        #endregion

        CodeMemberMethod GetCommandMethod(string sql, List<QueryInfo.Parameter> parameters)
        {
            CodeMemberMethod codeMemberMethod = new CodeMemberMethod();
            codeMemberMethod.Name = "GetCommand";
            codeMemberMethod.Attributes = MemberAttributes.Public | MemberAttributes.Final;
            codeMemberMethod.ReturnType = new CodeTypeReference(typeof(SqlCommand));

            codeMemberMethod.Parameters.Add(
                new CodeParameterDeclarationExpression(
                    new CodeTypeReference(
                        typeof(System.Data.SqlClient.SqlConnection)
                        ),
                    "connection"
                    )
                );
            AddCommandParameters(codeMemberMethod, parameters);

            // Command command = new Command();
            codeMemberMethod.Statements.Add(
                new CodeVariableDeclarationStatement(
                    new CodeTypeReference(typeof(System.Data.SqlClient.SqlCommand)),
                    "command",
                    new CodeObjectCreateExpression(
                        new CodeTypeReference(typeof(System.Data.SqlClient.SqlCommand)),
                        new CodeExpression[0])
                    )
                );

            #region Timeout Property

            // command.CommandTimeout = 300;
            if (m_dacGeneratorConfig.CommandTimeout.HasValue)
            {
                codeMemberMethod.Statements.Add(
                    new CodeAssignStatement(
                        new CodePropertyReferenceExpression(
                            new CodeVariableReferenceExpression(
                                "command"
                                ),
                            "CommandTimeout"
                            ),
                        new CodePrimitiveExpression(
                            m_dacGeneratorConfig.CommandTimeout.Value
                            )
                        )
                    );
            }
            else
            {
                codeMemberMethod.Statements.Add(
                    new CodeAssignStatement(
                        new CodePropertyReferenceExpression(
                            new CodeVariableReferenceExpression(
                                "command"
                                ),
                            "CommandTimeout"
                            ),
                        new CodeVariableReferenceExpression(
                            "CommandTimeout"
                            )
                        )
                    );
            }

            #endregion

            // command.Connection = connection;
            codeMemberMethod.Statements.Add(
                new CodeAssignStatement(
                    new CodeFieldReferenceExpression(
                        new CodeVariableReferenceExpression(
                            "command"
                            ),
                        "Connection"
                        ),
                    new CodeVariableReferenceExpression(
                        "connection"
                        )
                    )
                );

            #region Command Text Property

            // command.Text = "...";
            codeMemberMethod.Statements.Add(
                new CodeAssignStatement(
                    new CodeFieldReferenceExpression(
                        new CodeVariableReferenceExpression("command"),
                        "CommandText"), new CodeVariableReferenceExpression("CommandText")
                    )
                );

            #endregion

            #region Check for @@identity in the CommandText

            CodeThrowExceptionStatement throwIdentityException = new CodeThrowExceptionStatement(
                    new CodeObjectCreateExpression(
                        new CodeTypeReference(typeof(System.ArgumentException)),
                        new CodeSnippetExpression("\"You cannot use @@IDENTITY in any query, please use SCOPE_IDENTITY() instead.\"")
                    )
            );

            CodeConditionStatement checkIdentity = new CodeConditionStatement(
                new CodeMethodInvokeExpression(
                    new CodeMethodReferenceExpression(
                        new CodeFieldReferenceExpression(
                            new CodeVariableReferenceExpression(
                                "command"
                            ),
                            "CommandText.ToLowerInvariant()"
                        ),
                        "Contains"
                    ),
                    new CodeSnippetExpression("\"@@identity\"")
                ),
                throwIdentityException
            );

            codeMemberMethod.Statements.Add(checkIdentity);

            #endregion

            // command.CommandType = CommandType.Text;
            codeMemberMethod.Statements.Add(
                new CodeAssignStatement(
                    new CodeFieldReferenceExpression(
                        new CodeVariableReferenceExpression(
                            "command"),
                        "CommandType"),
                    new CodeFieldReferenceExpression(
                        new CodeTypeReferenceExpression(
                            typeof(System.Data.CommandType)
                            ),
                        System.Data.CommandType.Text.ToString()
                        )
                    )
                );

            int index = 0;

            foreach (QueryInfo.Parameter parameter in parameters)
            {
                string parameterName = parameter.FormattedName;

                // SqlParameter parameter0 = new SqlParameter();
                codeMemberMethod.Statements.Add(
                    new CodeVariableDeclarationStatement(
                        new CodeTypeReference(
                            typeof(System.Data.SqlClient.SqlParameter)
                            ),
                        "parameter" + index.ToString(),
                        new CodeObjectCreateExpression(
                            new CodeTypeReference(
                                typeof(System.Data.SqlClient.SqlParameter)
                                ),
                            new CodeExpression[0]
                            )
                        )
                    );

                // parameter0.ParameterName = "...";
                codeMemberMethod.Statements.Add(
                    new CodeAssignStatement(
                        new CodeFieldReferenceExpression(
                            new CodeVariableReferenceExpression(
                                "parameter" + index.ToString()
                                ),
                            "ParameterName"
                            ),
                        new CodePrimitiveExpression(
                            parameter.Name)
                        )
                    );

                // parameter0.Value = [parameterName less '@'];
                if ((parameter.Nullable == false) &&
                    (parameter.Type.IsValueType == true))
                {
                    // non nullable value type parameter

                    codeMemberMethod.Statements.Add(
                        new CodeAssignStatement(
                            new CodeFieldReferenceExpression(
                                new CodeVariableReferenceExpression(
                                    "parameter" + index.ToString()
                                    ),
                                "Value"
                                ),
                            new CodeVariableReferenceExpression(
                                parameterName
                                )
                            )
                        );
                }
                else if (parameter.Type.IsValueType == true)
                {
                    // nullable value type parameter

                    codeMemberMethod.Statements.Add(
                        new CodeConditionStatement(
                            new CodePropertyReferenceExpression(
                                new CodeVariableReferenceExpression(parameterName),
                                "HasValue"
                                ),
                            new CodeStatement[]
								{
								new CodeAssignStatement(
									new CodeFieldReferenceExpression(
										new CodeVariableReferenceExpression(
											"parameter" + index.ToString()
											),
										"Value"
										),
									new CodePropertyReferenceExpression(
										new CodeVariableReferenceExpression(
											parameterName
											),
										"Value"
										)
									)
								},
                            new CodeStatement[]
								{
								new CodeAssignStatement(
									new CodeFieldReferenceExpression(
										new CodeVariableReferenceExpression(
											"parameter" + index.ToString()
											),
										"Value"
										),
									new CodeFieldReferenceExpression(
										new CodeTypeReferenceExpression(
											typeof(System.DBNull)
											),
										"Value"
										)
									)
								}
                            )
                        );
                }
                else
                {
                    // reference type parameter

                    codeMemberMethod.Statements.Add(
                        new CodeConditionStatement(
                            new CodeBinaryOperatorExpression(
                                new CodeVariableReferenceExpression(
                                    parameterName
                                    ),
                                CodeBinaryOperatorType.IdentityInequality,
                                new CodePrimitiveExpression(
                                    null
                                    )
                                ),
                            new CodeStatement[]
								{
								new CodeAssignStatement(
									new CodeFieldReferenceExpression(
										new CodeVariableReferenceExpression(
											"parameter" + index.ToString()
											),
										"Value"
										),
									new CodeVariableReferenceExpression(
										parameterName
										)
									)
								},
                            new CodeStatement[]
								{
								new CodeAssignStatement(
									new CodeFieldReferenceExpression(
										new CodeVariableReferenceExpression(
											"parameter" + index.ToString()
											),
										"Value"
										),
									new CodeFieldReferenceExpression(
										new CodeTypeReferenceExpression(
											typeof(System.DBNull)
											),
										"Value"
										)
									)
								}
                            )
                        );

                    // we can only have the input/output parameters on a non-static method as the parameters
                    // will be properties of the BusinessClass or DACGenerated class

                    #region Add out Input/Output parameters

                    if (parameter.CommandOutputVariable)
                    {
                        // Sorry for using a code snippet statement but i really couldn't be bothered writing it all 
                        codeMemberMethod.Statements.Add(new CodeSnippetStatement(string.Format("\t\t\tparameter{0}.Direction = System.Data.ParameterDirection.Output;", index)));
                        codeMemberMethod.Statements.Add(new CodeSnippetStatement(string.Format("\t\t\tparameter{0}.Size = 4000;", index)));
                    }

                    #endregion

                }

                // command.Parameters.Add(parameter0);
                codeMemberMethod.Statements.Add(
                    new CodeMethodInvokeExpression(
                        new CodeMethodReferenceExpression(
                            new CodeFieldReferenceExpression(
                                new CodeVariableReferenceExpression(
                                    "command"
                                    ),
                                "Parameters"
                                ),
                            "Add"
                            ),
                        new CodeExpression[]
							{
							new CodeVariableReferenceExpression(
								"parameter" + index.ToString()
								)
							}
                        )
                    );


                index++;
            }

            // return command;
            codeMemberMethod.Statements.Add(
                new CodeMethodReturnStatement(
                    new CodeVariableReferenceExpression(
                        "command")
                    )
                );

            return codeMemberMethod;
        }

        CodeMemberProperty GetConnectionStringProperty(string connectionStringName)
        {
            CodeMemberProperty codeMemberProperty = new CodeMemberProperty();
            codeMemberProperty.Attributes = MemberAttributes.Override | MemberAttributes.Family;
            codeMemberProperty.Name = "ConnectionStringName";
            codeMemberProperty.Type = new CodeTypeReference(typeof(string));

            codeMemberProperty.GetStatements.Add(
                new CodeMethodReturnStatement(
                    new CodePrimitiveExpression(connectionStringName)
                ));

            return codeMemberProperty;
        }

        void AddCommandParameters(CodeMemberMethod method, List<QueryInfo.Parameter> parameters)
        {
            foreach (QueryInfo.Parameter parameter in parameters)
            {
                CodeTypeReference parameterType = (parameter.Nullable && parameter.Type.IsValueType) ?
                    new CodeTypeReference(
                        "System.Nullable",
                        new CodeTypeReference[]
							{
							new CodeTypeReference(
								parameter.Type
								)							
							}
                        ) :
                    new CodeTypeReference(
                        parameter.Type
                        );


                // [.net type] [parameterName minus '@']
                method.Parameters.Add(new CodeParameterDeclarationExpression(parameterType, parameter.FormattedName));
            }
        }

        public enum GenerateOutputType
        {
            DataAccessWithBusinessLogic,
            DataAccessOnly,
            BusinessLogicOnly
        }

        public string Generate(string sql, List<QueryInfo.Parameter> parameters,
            List<QueryInfo.OutputColumn> outputColumns,
            DacGeneratorConfig dacGeneratorConfig,
            GenerateOutputType generateOutputType,
            QueryInfo.QueryTables queryTables)
        {
            m_fileNamespace = dacGeneratorConfig.ClassConfig.Namespace;
            m_className = dacGeneratorConfig.ClassConfig.Class;
            m_dacGeneratorConfig = dacGeneratorConfig;
            m_fullNamespaceWithoutLogicPrefix = dacGeneratorConfig.ClassConfig.Namespace + "." + m_className;
            m_generateOutputType = generateOutputType;
            m_queryTables = queryTables;

            CodeNamespace codeNamespace = new CodeNamespace(dacGeneratorConfig.ClassConfig.Namespace);

            if (generateOutputType == GenerateOutputType.BusinessLogicOnly)
            {
                m_className += LOGIC_CLASS_SUFFIX;
                if (codeNamespace.Name.Contains(".DataAccess"))
                {
                    codeNamespace.Name = codeNamespace.Name.Replace(".DataAccess", "." + LOGIC_CLASS_NAMESPACE.Replace(".", ""));
                }
                else
                {
                    codeNamespace.Name += LOGIC_CLASS_NAMESPACE;
                }
            }

            // first split out CommandOutputVariables on a comma so we can find all matches and mark it 
            foreach (string commandOutputVariable in dacGeneratorConfig.DacEntityMethodGeneratorConfig.CommandOutputVariables.Split(",".ToCharArray()))
            {
                // use a predicate to match the column
                Destra.Tools.DACGenerator.QueryInfo.Parameter outputColumn = parameters.Find(new Destra.Tools.DACGenerator.QueryInfo.Predicates().FindParameterByName(commandOutputVariable));
                if (outputColumn != null)
                {
                    // set the column as an output column
                    outputColumn.CommandOutputVariable = true;
                    hasCommandOutputParameter = true;
                }
            }

            // clear any output columns if we have specified the type as an input entity method or none
            if (dacGeneratorConfig.DacEntityMethodGeneratorConfig.DacEntityMethodType == DacEntityMethodType.Input || dacGeneratorConfig.DacEntityMethodGeneratorConfig.DacEntityMethodType == DacEntityMethodType.None)
            {
                outputColumns = new List<QueryInfo.OutputColumn>();
            }

            CodeTypeDeclaration dacClass = GetClass(sql, parameters, outputColumns, dacGeneratorConfig.DacEntityMethodGeneratorConfig.DacEntityMethodType, dacGeneratorConfig.DacEntityMethodGeneratorConfig.EntityName, false, dacGeneratorConfig.RunTimeConnectionStringName, generateOutputType);
            dacClass.CustomAttributes.Add(
                new CodeAttributeDeclaration(
                    new CodeTypeReference(
                        typeof(System.ComponentModel.DataObjectAttribute)
                        )
                    )
                );
            codeNamespace.Types.Add(dacClass);

            if (dacGeneratorConfig.DacEntityMethodGeneratorConfig.CachingEnabled && !string.IsNullOrEmpty(dacGeneratorConfig.DacEntityMethodGeneratorConfig.CachingType))
            {
                Resources.ExecuteEmbeddedResource.ExecuteEmbeddedSqlFile(m_dacGeneratorConfig.ConnectionString, "AspNet_SqlCachePollingStoredProcedure.sql");
                Resources.ExecuteEmbeddedResource.ExecuteEmbeddedSqlFile(m_dacGeneratorConfig.ConnectionString, "AspNet_SqlCacheQueryRegisteredTablesStoredProcedure.sql");
                Resources.ExecuteEmbeddedResource.ExecuteEmbeddedSqlFile(m_dacGeneratorConfig.ConnectionString, "AspNet_SqlCacheTablesForChangeNotification.sql");
                Resources.ExecuteEmbeddedResource.ExecuteEmbeddedSqlFile(m_dacGeneratorConfig.ConnectionString, "AspNet_SqlCacheUpdateChangeIdStoredProcedure.sql");

                foreach (QueryInfo.QueryTables.Table table in queryTables.Dependencies)
                {
                    Resources.ExecuteEmbeddedResource.ExecuteEmbeddedSqlFile(m_dacGeneratorConfig.ConnectionString, "CacheTrigger.sql", table.Schema, table.TableName); 
                }

            }

            return base.GenerateCodeFromNamespace(codeNamespace);
        }
    }
}
